Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Hello -
I'm hoping some T-SQL expert could help me figure out the code
necessary to implement a stored procedure that will truncate and
shrink the log files when run.
Let me give you a little background first. We are a small company
with an adequate, but not overwhelming amount of disk space. But,
with running SQL Server 2000 we do like the Recovery model set to
Full. Each week on back to back evenings (and on separate tapes) we
do a full backup with incremental backups throughout the week. After
the 1st full backup, I would like to run a procedure that would look
at each database and then truncate & shrink the log file. Then for
the 2nd full backup, I'd have much smaller logs obviously with the
ability to recover back to my original(s) via my 1st backup.
The code that works on individual databases that I now run manually
and change the parameters (@.database) accordingly is:
use master
BEGIN
BACKUP LOG @.database WITH TRUNCATE_ONLY
GO
USE @.database
DBCC SHRINKFILE (@.database + '_log')
I would like to have this placed in a stored procedure that would go
through each user database automatically. Unfortunately, I am not at
all familiar with the nuances of automatically changing databases nor
with working with stored procedures.
If anyone would/could be so kind as to provide me with the script that
would allow this to work as desired, I would be very very
appreciative. Thanks in advance for any and all help!
RichScull,
I am a little confused as to what your actually doing. You say you do a
FULL backup and then incremental but do not specify log backups at all other
than the truncate only. If you don't actually do log backups why do you
want to be in FULL recovery mode? What good does shrinking the logs do
anyway? When you backup a log (or a database) the size of the backup file
is proportional to the amount of data in the file not the size of the file
itself. So shrinking a log file will not give smaller backups. Shrinking
it and of itself is a dangerous act anyway for the reasons you mention. If
you have limited space and you shrink the file, which will certainly grow
back again, what will happen when you need more space and don't have it? It
is better to allocate the space in need (now and in the future) up front and
don't ever shrink the files. That way you ensure you always have the room
when you need it. If I misinterpreted what your doing please let me know so
we can work thru this.
Andrew J. Kelly
SQL Server MVP
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Scull
Perhaps you want to use WITH INIT (after full backup database) to ovewrite
the log file.
Also,look at INFORMATION_SCHEMA.SCHEMATA in BOL.
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||First off, I want to thank you for your input- it's geratly appreciated!
I'm sorry I wasn't clear in terms of how we perform our backups. We use
a separate backup application for backing up all the SQL Server
databases and their respective transaction logs. We do FULL backups on
Friday and Saturday evening, and incremental backups throughout the
week.
My space issues arose when I converted all of my 7.0 databases to 2000
about 4 months ago. Rather quickly after converting the db's, I noticed
we were running out of disk space. In reviewing one of the converted
databases that was affecting my disk space - I noticed the MDF file with
a size of about 1 GIG and the transaction log with a size of 17 GIG.
Obviously, I was like "Yikes."
Anyway, at this point in time I'd would rather keep the recovery mode
set to full so that we could perform point-in-time recoveries. I figure
the best way, given my disk space concerns, for me to insure possible
point-in-time recovery is to take advantage of the fact that 2 full
backups are done each week. If after the 1st full backup, I can
truncate and shrink the transaction log before the 2nd full backup - I
will have a choice of restore points in the event something should go
awry.
Now while I know this is not necessarily the optimal solution, I do not
know of a better one at this point. I am obviously wide open to
suggestions/recommendations.
If no strategic process changes can be realized, I think I need to find
the answer to my T-SQL question concerning establishing a stored
procedure that I can schedule after the Friday backup but before the
Saturday backup - that will truncate and shrink the transaction log(s)
for each of the user databases automatically.
I will also check out the WITH INIT, etc. in the BOL.
Thanks in advance for any more feedback.
Rich
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Rich,
Your solution is to issue regular Log backups and forget about truncating
and shrinking the logs. If you want point in time recovery you have to be
in FULL mode. If your in FULL mode the log file will keep growing and
growing since it needs a log backup to properly backup the committed trans
and truncate them. If you don't issue log backups what will happen if your
disk array dies and you must restore from scratch? You will only have up to
your last full and incremental backup. You have lost your ability to
restore up to the minute or point in time. If you issue regular log backups
your tran log itself will stay at a size that it needs over a point it time
and should never be shrunk once it reaches that quiescent state. Of coarse
you log backup files will now contain the data that used to be in your log
so you need a place to store those and hopefully it's not on the same array
as the db. That should solve your disk space issue and alleviate the need
to schedule a log truncation and shrinking. Just schedule regular log
backups. The interval is up to you. Every 15 minutes or 1/2 hour should
keep your log file from growing too much and keep you relatively safe as
well. If your concerned about room to place your backup files you might
consider using a product called SQL LiteSpeed. It will reduce the time for
a backup and give you much smaller files to boot.
Andrew J. Kelly
SQL Server MVP
"Rich S" <myscullyfamily@.yahoo.com> wrote in message
news:O1Kiq8d8DHA.3112@.tk2msftngp13.phx.gbl...
> First off, I want to thank you for your input- it's geratly appreciated!
> I'm sorry I wasn't clear in terms of how we perform our backups. We use
> a separate backup application for backing up all the SQL Server
> databases and their respective transaction logs. We do FULL backups on
> Friday and Saturday evening, and incremental backups throughout the
> week.
> My space issues arose when I converted all of my 7.0 databases to 2000
> about 4 months ago. Rather quickly after converting the db's, I noticed
> we were running out of disk space. In reviewing one of the converted
> databases that was affecting my disk space - I noticed the MDF file with
> a size of about 1 GIG and the transaction log with a size of 17 GIG.
> Obviously, I was like "Yikes."
> Anyway, at this point in time I'd would rather keep the recovery mode
> set to full so that we could perform point-in-time recoveries. I figure
> the best way, given my disk space concerns, for me to insure possible
> point-in-time recovery is to take advantage of the fact that 2 full
> backups are done each week. If after the 1st full backup, I can
> truncate and shrink the transaction log before the 2nd full backup - I
> will have a choice of restore points in the event something should go
> awry.
> Now while I know this is not necessarily the optimal solution, I do not
> know of a better one at this point. I am obviously wide open to
> suggestions/recommendations.
> If no strategic process changes can be realized, I think I need to find
> the answer to my T-SQL question concerning establishing a stored
> procedure that I can schedule after the Friday backup but before the
> Saturday backup - that will truncate and shrink the transaction log(s)
> for each of the user databases automatically.
> I will also check out the WITH INIT, etc. in the BOL.
> Thanks in advance for any more feedback.
> Rich
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Rich
I suggest you setup a SQL 2000 Agent Job executing the following TSQL
command:
DBCC SHRINKFILE(2, TRUNCATEONLY)
on the DB(s) in question. Set it to run off hours, the "2" should be
for the
Log(LDF) file, a "1" should be the for MDF file.
I'm still waiting myself to see a script that will parse thru the DBs
on a server and build the DBCC above for every DB.
good luck
Steven|||You could do it vb6 or .net using
Public oSQLServer As New SQLDMO.SQLServer
For Each oDatabase In oSQLServer.Databases
'If odatabase.Status <> SQLOLEDBStat_Inaccessible Then
form1.List_Databases.AddItem oDatabase.Name
'frmExecVB.lstCat.AddItem oDatabase.Name
'End If
Next oDatabase
Then you can build up a string using filesystem object to write your
script for you.
Have a look at SQLDMO Object, it rocks !
I've even got some code to look round your network and come back with a
list of sql servers...
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Thanks for your reply. I have still been working on a solution and have
not had the opportunity to write back. I work for a relatively small
company where I were a number of 'hats' of which being the DBA is just
one of them.
Anyway, I setup transaction log backups hourly throughout the day (I
don't have that many transactions - but do have a lot of databases) and
that is working fine. In tracking the transaction log size, I was able
to summize that it grew immensely on late Saturday evenings - which is
the time I kick off my database maintenance plan which includes
rebuilding indexes and optimizing the databases.
For example, one particular database who MDF is 540 mb and had a
transaction log of 52 mb throughout the week - had it's transaction log
grow to 468 mb after running the maintenance plan.
I am thinking that I should then 1) backup the transaction log with
truncate only, then 2) run the SHRINKFILE command to reduce the
transaction log to say... 50 mb. 3) Then backup the entire database.
If that works, I would need to incorporate that into all of my db plans.
Any thoughts/concerns? Thanks again for everyone's assitance!!!
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||try the following to truncate TLogs:
--drop procedure xxx_SQL_Logspace_truncate_all
--CREATE procedure xxx_SQL_Logspace_truncate_all as
USE Master
DECLARE @.logname varchar(20)
DECLARE @.query varchar(255)
DECLARE LogList_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
ORDER BY name
OPEN LogList_cursor
FETCH NEXT FROM LogList_cursor INTO @.logname
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- add DB's you want to skip to the following list
IF RTRIM(@.logname) = "master" or
RTRIM(@.logname) = "model" or
RTRIM(@.logname) = "msdb" or
RTRIM(@.logname) = "pubs" or
RTRIM(@.logname) = "Northwind"
print @.logname + ' skipped'
ELSE
begin
Set @.query = "DBCC SHRINKFILE("+ RTRIM(@.logname) + ".LDF, TRUNCATEONLY)"
select @.query
end
FETCH NEXT FROM LogList_cursor into @.logname
END
-- following will list all DB's Logspace stats
DBCC SQLPERF(LOGSPACE)
CLOSE LogList_cursor
DEALLOCATE LogList_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Hello -
I'm hoping some T-SQL expert could help me figure out the code
necessary to implement a stored procedure that will truncate and
shrink the log files when run.
Let me give you a little background first. We are a small company
with an adequate, but not overwhelming amount of disk space. But,
with running SQL Server 2000 we do like the Recovery model set to
Full. Each week on back to back evenings (and on separate tapes) we
do a full backup with incremental backups throughout the week. After
the 1st full backup, I would like to run a procedure that would look
at each database and then truncate & shrink the log file. Then for
the 2nd full backup, I'd have much smaller logs obviously with the
ability to recover back to my original(s) via my 1st backup.
The code that works on individual databases that I now run manually
and change the parameters (@.database) accordingly is:
use master
BEGIN
BACKUP LOG @.database WITH TRUNCATE_ONLY
GO
USE @.database
DBCC SHRINKFILE (@.database + '_log')
I would like to have this placed in a stored procedure that would go
through each user database automatically. Unfortunately, I am not at
all familiar with the nuances of automatically changing databases nor
with working with stored procedures.
If anyone would/could be so kind as to provide me with the script that
would allow this to work as desired, I would be very very
appreciative. Thanks in advance for any and all help!
RichScull,
I am a little confused as to what your actually doing. You say you do a
FULL backup and then incremental but do not specify log backups at all other
than the truncate only. If you don't actually do log backups why do you
want to be in FULL recovery mode? What good does shrinking the logs do
anyway? When you backup a log (or a database) the size of the backup file
is proportional to the amount of data in the file not the size of the file
itself. So shrinking a log file will not give smaller backups. Shrinking
it and of itself is a dangerous act anyway for the reasons you mention. If
you have limited space and you shrink the file, which will certainly grow
back again, what will happen when you need more space and don't have it? It
is better to allocate the space in need (now and in the future) up front and
don't ever shrink the files. That way you ensure you always have the room
when you need it. If I misinterpreted what your doing please let me know so
we can work thru this.
--
Andrew J. Kelly
SQL Server MVP
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Scull
Perhaps you want to use WITH INIT (after full backup database) to ovewrite
the log file.
Also,look at INFORMATION_SCHEMA.SCHEMATA in BOL.
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Rich
I suggest you setup a SQL 2000 Agent Job executing the following TSQL
command:
DBCC SHRINKFILE(2, TRUNCATEONLY)
on the DB(s) in question. Set it to run off hours, the "2" should be
for the
Log(LDF) file, a "1" should be the for MDF file.
I'm still waiting myself to see a script that will parse thru the DBs
on a server and build the DBCC above for every DB.
good luck
Steven

Creating a Subtotal of select Groups

Hi,

I am working on a new reporting system using reporting services, but I cannot figure out how to create a footer row which will only subtotal select group totals. If anyone has a method to do this please help!

Nathan

If you have a matrix report right click on the group and select the option subtotal.
If you have a tabular report right click on the left side of the table and select table footer. Then in each field you want to summarize put = SUM(Fields!FieldName.Value)
That's all|||

I've been able to do that for individual groups, but what I want to do is make footer subtotal of a select set of groups. So say I have data grouped by Credit card type. I have a group for MC, and another for Visa, and another for American express.

I want a footer total of just the MC and Visa groups, excluding the total for American express.

|||You could use an expression similar to this in the footer (it will add 0 instead of the actual amount if the card was Amex):
=Sum(iif(Fields!CardType.Value = "Amex", 0, Fields!TransactionAmount.Value))

--Robert|||Alright!

Thank you Robert. you made my day |||

When running this selective sum, i get a scope error. I have tried giving it a group name and a dataset. What am i doing wrong? here is my code:

=Sum(iff(Fields!CardType.Value = "Visa/Mc" OR Fields!cardType.Value = "Diner" OR Fields!cardType.Value = "JCB", Fields!amount.Value, 0))
This is my error:

"The value expression for the textbox ‘textbox9’ refers to the field ‘CardType’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Whats wrong?

|||I have the same problem. I don't want to sum a select number of groups, but all groups within the report. The principle is the same as the above, and I get the same scope error.
In my case I have a bunch of items grouped by customer. Each customer has a subtotal, and I want to have a grand total of all the customers.
Any ideas/workarounds?|||Note: Field names are case-sensitive. In your expression it seems like you have upper-case and lower-case "CardType" fields.
Also, are the cardType field and the amount field in the same dataset?

-- Robert|||If you want to get the grand total, you just need to specify either the data region name (i.e. table, list, or matrix report item name) or the data set name.
E.g.
=Sum(Fields!Amount.Value, "DataSet1")

-- Robert|||Thanks, Robert! I knew it had to be something simple. |||Hi, somehow related with the topic:
Is posible to have something like: the sum of the ValueField from all the rows of DataSet2 that have CompareFiled equal with the current value of ComparedWithField from DataSet1?
In other words: in the expression of the SUM function can be used more then one scope?
=Sum(iff( DS2!Fields!CardType.Value = Fields!CT.Value, DS2!Fields!amount.Value, 0))

Creating a publication

Hi,
I've come across this one before in one of the threads, unfortunately for me the author managed to figure it out for his/her self and there for no answer was posted, so can anybody help PLEASE?

Creating Publication

- Creating Publication 'SQLMobile' (Error)
Messages
* SQL Server could not create publication 'SQLMobile'. (New Publication Wizard)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Invalid object name 'dbo.sysmergepublications'.
Publication 'SQLMobile' does not exist.
Changed database context to 'SQLMobile'. (Microsoft SQL Server, Error: 208)

Its been created via UI

on SQL2005
the distributor is correctly installed

i also have not tried creating a publication on any other database.....this is the first.

Any Ideas?

try this

sp_replicationdboption 'SQLMobile','merge publish','true'

Creating a publication

Hi,
I've come across this one before in one of the threads, unfortunately for me the author managed to figure it out for his/her self and there for no answer was posted, so can anybody help PLEASE?

Creating Publication

- Creating Publication 'SQLMobile' (Error)
Messages
* SQL Server could not create publication 'SQLMobile'. (New Publication Wizard)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Invalid object name 'dbo.sysmergepublications'.
Publication 'SQLMobile' does not exist.
Changed database context to 'SQLMobile'. (Microsoft SQL Server, Error: 208)

Its been created via UI

on SQL2005
the distributor is correctly installed

i also have not tried creating a publication on any other database.....this is the first.

Any Ideas?

try this

sp_replicationdboption 'SQLMobile','merge publish','true'

creating a database, tables, records, and fields in a Sql server 2005 express

I am at my wits end as to how to do this.
I have downloaded Sql server management Studio and tried to create a database but I can't figure it out. There is an almost nonexistant help file so I am lost as to how to start. I have succeeded in looking at some system databases but that is about the extent of it. Can someone show me the proper procedure? Am I using the wrong tool?
Thanks,
Paul

If you are using the managment studio, right click on the Databases node, --> New Database. If you are using a script tool for this, the fastest way to create a database is CREATE DATABASE <name>, which stores the data files in the defautl location, specified at setup time.

HTH, Jens Suessmeyer.

|||Thanks, Jen! boy was that ever easy! I've got tables and columns now too! Tell me though, can I input some data in the management Studio Also?
Thanks,
Paul
|||Well I think I spoke too soon. Now in VWD I drop a gridview and try to connect it to my database. When I am building a connection string, I get the dialog box, "Add Connection. When I try to browse and select my database I get this real long error, the essence of "Unable to open the phsical file c:\blah|blah\blah\prince.mdb. Operating system error 32(The process cannot access the file because it is being used by another process)" I know that it isn't being used because I closed the database and exited Management Studio.
Thanks,
Paul
|||

Make sure the database is AutoClose=true. If the database is not set to automatically close, the SQL Server service will keep it open as long as the server is running.

You can check this in the Options page of the database properties dialog in Management Studio.

Hope this helps,

Steve

|||Thanks, Steve. That was the problem. However, I still can't add data or insert blank records in the database. I noticed when I used an Access database I was able to edit and delete the data. I would like to be able to do that with Sql server express database also and in addition to inserting a blank record. I am using a a gridview component.

In Visual basic 6.0 I could drop a few buttons on the form and with next, previous delete and insert methods I could alter the database where the columns were bound to individule Textboxes.
I have a lot to learn and there are very few books and tutorials written with info on what I want to learn.

I guess the gridview tasks are limited because I have no data to edit or delete, and I don't know how to use the Management Studio to enter the data. :(
|||I tried executing a query like this:
INSERT INTO family VALUES ("Paul", "Handley"); family is my table and there are only two columns. I tried putting the column names but got an error. I also get this error when I try to run the above query:
Msg 128, Level 15, State 1, Line 1
The name "Paul" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Any suggestions?
Paul
|||

You can either set the following command:

SET QUOTED_IDENTIFIER is OFF

to use double quotation marks, or you use single quotation marks which wont cause the problem.

HTH, jens Suessmeyer.

|||Thanks, Jen. That did the trick. As far as the inserting and the editing of a database record within the web application I am developing, I still can't do that. Is it possible to attach a query to a button and just insert a blank record. Like this:
Insert into family Values( '','') **Those are single quotes**
Thanks,
Paul
|||Ok I've got to the point where when I am configuring the datasource and I get to the "Configure the select statement and I choose "Specify columns from a table or view. I click on te advanced tab and I want to choose"Generate Insert, Update and Delete Statements" but the whole dialog box is grayed out! What do I do now? I want the capabilities of editing, insertsing, deleteing and updating my records
Thanks,
Paul

|||Ok I found the where I could add the various links to Update Insert and edit my fields and when I click on New I get the blank fields to add new data but when I click on update I get this error msg:

Inserting is not supported by data source 'SqlDataSource1' unless InsertCommand is specified.

I feel sure If I could fix this I would be on my way, but the as I stated above the whe option are grayed out!

Will someone pleeese answer this post!

|||Ok, that's it. If you are going to just leave me hanging here, I'm through. I'm going back to Linux and PHP and Mysql. I should have know better than to try this Microsoft garbage!
Paul
|||

Hi Paul,

there is no need yelling in this group. As we are doing this all in our spare free time and on a voluntary basis, the poster of a question sometimes has to wait until he gets an answer. IMHO I would rather learn the basis things first, before comming to the groups and posting question which would have been solved if you just took a walkthough-easy-to-use-example.

The question you are posting is related that you appearantly didn′t specifiy which command has to be executed in the case of an insert. YOu can either specify your own command or use a commanbuilder (But that are , as I told below some basic things which wouldn′t concern you if you have done some ADO.NET basics first)

Fell free to come any time again in this group, but be aware that you sometime need to have a bit patience.

HTH; jens Suessmeyer.

|||p3aul,
your statement:
INSERT INTO family VALUES ("Paul", "Handley");
should be:
INSERT INTO family (fieldname, fieldname) VALUES ("Paul", "Handley");
where fieldname is the name of your field.
|||Well I wasn't aware that I was shouting, I just c & p the error that appeared on the webpage. i figured it would just paste as normal 10 or 12 pt type. I enlarged the fon't and chose the color red in my plea for help because Time was going by and I was beginning to lose my train of thought.. I don't sit idley by and wait for someone to give me an idea that might help, I try to persue a solution on my on. If I leave the a file i am working on and load something else in and put my mind ont I am apt to forget what I was doing on the first file. I apologise for feeling frustrated but Microsoft tools are frustraing things to work with.

tonic999 Thank you for the post. I don't remember where I was now. In working with MySQL , though, in the insert statement (fieldname, fieldname) are optional.

My original question, If I can find the file I was working on, still stands, to wit:
Ok I've got to the point where when I am configuring the datasource and I get to the "Configure the select statement and I choose "Specify columns from a table or view. I click on te advanced tab and I want to choose"Generate Insert, Update and Delete Statements" but the whole dialog box is grayed out! What do I do now? I want the capabilities of editing, insertsing, deleteing and updating my records
Thanks,
Paul

Creating a database with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.
1)How are you connecting to the other databases ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.
|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:

> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> to
> seperate
> I
> me.
>
>

Creating a database with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.
1)How are you connecting to the other databases ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.
|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:

> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> to
> seperate
> I
> me.
>
>

Creating a database with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.1)How are you connecting to the other databases ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:

> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote
in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> to
> seperate
> I
> me.
>
>

Creating a database with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.1)How are you connecting to the other databases ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:

> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote
in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> to
> seperate
> I
> me.
>
>

Creating a database with link

Hello,
I am trying to figure out the best way to design a database that links to
external information like freedb.org and amazon, ect. I have created the
tables of the information I am looking for, but am wondering if I have to
create a table to link to those places to import the information in the
database I am trying to design. I think it is possible I have two seperate
questions in here. So I will try and breakdown to what I am looking for. I
have a database with specific information, I am looking for. I have media
that contains some, but not all of the information readily available to me.
Now what I want to do is take that bits and pieces of that information and
enter it into my current database and have it search other external
databases, to find the rest of the information and input that information
into my current database. Any help I would appreciate.1)How are you connecting to the other databases ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> Hello,
> I am trying to figure out the best way to design a database that links
to
> external information like freedb.org and amazon, ect. I have created the
> tables of the information I am looking for, but am wondering if I have to
> create a table to link to those places to import the information in the
> database I am trying to design. I think it is possible I have two
seperate
> questions in here. So I will try and breakdown to what I am looking for.
I
> have a database with specific information, I am looking for. I have media
> that contains some, but not all of the information readily available to
me.
> Now what I want to do is take that bits and pieces of that information and
> enter it into my current database and have it search other external
> databases, to find the rest of the information and input that information
> into my current database. Any help I would appreciate.|||Well I haven't connected to any other databases as of yet. But my
connections are set up for TCP/IP and Named pipes.
"Jack Vamvas" wrote:
> 1)How are you connecting to the other databases ?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Michael R. Mastro II" <MichaelRMastroII@.discussions.microsoft.com> wrote in
> message news:3D1BEE16-A914-44D6-864A-7B6289758826@.microsoft.com...
> > Hello,
> >
> > I am trying to figure out the best way to design a database that links
> to
> > external information like freedb.org and amazon, ect. I have created the
> > tables of the information I am looking for, but am wondering if I have to
> > create a table to link to those places to import the information in the
> > database I am trying to design. I think it is possible I have two
> seperate
> > questions in here. So I will try and breakdown to what I am looking for.
> I
> > have a database with specific information, I am looking for. I have media
> > that contains some, but not all of the information readily available to
> me.
> > Now what I want to do is take that bits and pieces of that information and
> > enter it into my current database and have it search other external
> > databases, to find the rest of the information and input that information
> > into my current database. Any help I would appreciate.
>
>

CREATE_ENDPOINT / HTTPRequest

I am trying to figure out how to communicate to some web clients when a
SQL Server table has changed. I am connecting to the database through
ADO embedded on the clients in web pages. I want to be able to refresh
data on the client only when needed. I have researched all sorts of
methodologies and it seams like the CREATE_ENDPOINT will allow for me
to create a connection where I can call some functions like;
getDataStatus and return to the client what date time the data last
changed and then allow my client to requery the database or, something
like, setAccessTime where the Client sends its last date time stamp
when It last queried for data and allow the server to send back the
requested dataset to the client.Synchronize clock, sync up the clocks
of the clients to server.
Any help would be greatly appreciated. DanHello Dan,
I don't think there's a great way to do this since Web Services is essential
ly
a pull rather than push approach. If you were working with traditional ASMX
functions, I'd suggest looking at SqlCacheDependency since that could "subsc
ribe"
to changes on a query without having to invest a lot in the infrastructure
of the solution. Your ASMX would cache the dataset (saving SQL Server genera
ting
it fresh or from cache) and would, more or less, automatically get updated
data whenever your query changed. You could then add your bits for tracking
when the last change was received.
There really shouldn't need to be a have the client do more periodically
"call home" and ask "have you got a new version of this data?" Use some seri
al
number approach instead of time so that you don't have to worry about timezo
ne
changes between the client and the server.
This probably works "better" in that it minimizes the load on SQL Server
and gives you a better programability model than just a native web service
does today. Not saying that these services aren't useful, but they are harde
r
to use concepts like SqlDepedency on.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks, I think I understand what you are getting at. There are no real
provisions to push to clients any data without request from the client
unless you take advantage of new .net technology which will allow for
caching of data on querys, tables etc... and automatically update the
data every ' as set up within the SQL or is it web config. Anyway. I
think I may have my clients poll for a serial number from an END_POINT
and then get the data when the serial number is different from the
clients. Do you think this will work. I wasn't sure if you were saying
it would or would not work? Thanks.|||Hello Dan,

> Thanks, I think I understand what you are getting at. There are no
> real provisions to push to clients any data without request from the
> client unless you take advantage of new .net technology which will
> allow for caching of data on querys, tables etc... and automatically
> update the data every '
There a new set of features in SQL Server 2005, .NET 2.0 and ASP.NET 2.0
allow a client to subscriber to notifications of changes on the data underly
ing
a query. In .NET, its common to use SqlDependency for this. In ASP.NET 2.0,
its common to use SqlCacheDependency. These subscribe to server-side events
and allow processing of them on the client side. However, they currently
require an active TDS connection to SQL Server.
When you get the notification, you can then decide what you want to do. A
common choice is to recall your loading code and restart the dependency.

> as set up within the SQL or is it web config.
Some of both. Google on SqlCacheDependency for more information.

> Anyway. I think I may have my clients poll for a serial number from an
> END_POINT and then get the data when the serial number is different
> from the clients. Do you think this will work. I wasn't sure if you
> were saying it would or would not work? Thanks.
No, because the endpoint doesn't have a good way to keep track of a serial
number. I'm basically suggesting that if you want an optimized solution (in
this case), don't use endpoints, use ASMX instead.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thank You, Ill look into it. Dan

Create View in SQL Server with data types

I need to create a view of a sql table, but change the data types. I know
the syntax below is not correct, and can't figure out if it is wrong or if
you just can't do this. I have only created views before with the same data
type.

CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))
INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01
FROM F0005 AS F0005
WHERE DRSY = '41' AND DRRT = 'S1')

Thanks!!Aren't those JD Edwards column names a drag? I'm surprised you need
UNICODE (nchar and nvarchar instead of char and varchar), I did not
realize that JDE could handle anything beyond EBCDIC or ASCII.

A VIEW is just a SELECT statement inside. The example has an INSERT
statement. A VIEW can NOT have an INSERT statement.

CREATE VIEW does not allow for specifying the data types in the VIEW
column list. It is not part of the syntax.

When I need to control the data type this way I use CONVERT, though
CASE can do the same thing:

CREATE VIEW F0005New
AS
SELECT CONVERT(nhcar(3), rtrim(F0005.DRKY)) as DRKY,
CONVERT(nchar(30), F0005.DRDL01) as DRDL01
FROM F0005
WHERE DRSY = '41'
AND DRRT = 'S1'

Roy Harvey
Beacon Falls, CT

On Mon, 14 Aug 2006 15:58:01 GMT, "cognosqueen" <u25284@.uwewrote:

Quote:

Originally Posted by

>I need to create a view of a sql table, but change the data types. I know
>the syntax below is not correct, and can't figure out if it is wrong or if
>you just can't do this. I have only created views before with the same data
>type.
>
>CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))
>INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01
>FROM F0005 AS F0005
>WHERE DRSY = '41' AND DRRT = 'S1')
>
>Thanks!!

|||Roy - thanks so much!!! You are right about the JE Edwards column names!! I
do not enjoy them at all. Regarding the data type - the client already had
it set up - I just have to live with it!! I'll try this and see if it works!

Roy Harvey wrote:

Quote:

Originally Posted by

>Aren't those JD Edwards column names a drag? I'm surprised you need
>UNICODE (nchar and nvarchar instead of char and varchar), I did not
>realize that JDE could handle anything beyond EBCDIC or ASCII.
>
>A VIEW is just a SELECT statement inside. The example has an INSERT
>statement. A VIEW can NOT have an INSERT statement.
>
>CREATE VIEW does not allow for specifying the data types in the VIEW
>column list. It is not part of the syntax.
>
>When I need to control the data type this way I use CONVERT, though
>CASE can do the same thing:
>
>CREATE VIEW F0005New
>AS
>SELECT CONVERT(nhcar(3), rtrim(F0005.DRKY)) as DRKY,
CONVERT(nchar(30), F0005.DRDL01) as DRDL01
FROM F0005
WHERE DRSY = '41'
AND DRRT = 'S1'
>
>Roy Harvey
>Beacon Falls, CT
>

Quote:

Originally Posted by

>>I need to create a view of a sql table, but change the data types. I know
>>the syntax below is not correct, and can't figure out if it is wrong or if

>[quoted text clipped - 7 lines]

Quote:

Originally Posted by

>>
>>Thanks!!

|||Roy - it worked! Thanks for your help.

cognosqueen wrote:

Quote:

Originally Posted by

>Roy - thanks so much!!! You are right about the JE Edwards column names!! I
>do not enjoy them at all. Regarding the data type - the client already had
>it set up - I just have to live with it!! I'll try this and see if it works!
>

Quote:

Originally Posted by

>>Aren't those JD Edwards column names a drag? I'm surprised you need
>>UNICODE (nchar and nvarchar instead of char and varchar), I did not

>[quoted text clipped - 25 lines]

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>
>>>Thanks!!

|||You are welcome, and good luck! My experience with importing JD
Edwards data into a SQL Server data warehouse was quite painful, not
because of F0005 which in our case was quite clean, but the invoice
data that came in lacked any unique key. I hope your life is simpler
than mine was.

Roy

On Mon, 14 Aug 2006 18:37:37 GMT, "cognosqueen" <u25284@.uwewrote:

Quote:

Originally Posted by

>Roy - it worked! Thanks for your help.
>
>cognosqueen wrote:

Quote:

Originally Posted by

>>Roy - thanks so much!!! You are right about the JE Edwards column names!! I
>>do not enjoy them at all. Regarding the data type - the client already had
>>it set up - I just have to live with it!! I'll try this and see if it works!
>>

Quote:

Originally Posted by

>>>Aren't those JD Edwards column names a drag? I'm surprised you need
>>>UNICODE (nchar and nvarchar instead of char and varchar), I did not

>>[quoted text clipped - 25 lines]

Quote:

Originally Posted by

>>>>
>>>>Thanks!!