Backing up log when recovery set to Simple

I have a job that I created that backs up transaction logs on a server that has many databases on it. If a db recovery model is set to simple, I want the job to skip over that database, rather then trying to backup the log.

It would be easy to just let it try, and fail, however, the job status will show failed, even if it backs up all database logs, and only fails on one. Is there a way to easily programatically detect if a db is set to simple, and then I can skip backing up that db log?

Thanks
TommySELECT DATABASEPROPERTYEX('Northwind','Recovery')|||Thanks brett, that's what I needed, in case anyone else is interested, here's the syntax I'm using:

Declare @.Recovery_Model nvarchar(20),
Select @.Recovery_Model = CONVERT(nvarchar(20), DATABASEPROPERTYEX( @.Database_Name , 'Recovery' ))
if (@.Recovery_Model = 'SIMPLE')
PRINT 'Simple Mode so skip'
else
PRINT 'do the needful'

Backing up 2 databases..

Hi,
Is it ok to backup 2 databases in one file. Our vb.net app uses 2 databases (created on MSDE) and we want to prompt user to do backup during uninstallation but do not want to ask them twice (for each database). Then later on we want to be able to restor
e from the backup files too.
Any suggestions.
Thanks
hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:9A3740A3-E7C2-44B6-B717-29682B262BD5@.microsoft.com...
> Hi,
> Is it ok to backup 2 databases in one file. Our vb.net app uses 2
databases (created on MSDE) and we want to prompt user to do backup during
uninstallation but do not want to ask them twice (for each database). Then
later on we want to be able to restore from the backup files too.
> Any suggestions.
> Thanks
each backup file can contain more than 1 single database backup set, even
from multiple databases...
you have to specify the syntax as
BACKUP DATABASE databasename
TO DISK = N'c:\..\phisical path.bck'
WITH INIT
for the very first backup, in order to insure it overwrites any existing
one, while you have to omit the WITH INIT clause for additional database
backup set you want to include in the same backup device..
in order to restore from it, you have to use the syntax as
RESTORE DATABASE databasename
FROM DISK = N'c:\..\phisical path.bck'
WITH FILE = n
where n is the backup set file position within the backup device...
you can see the whole content of your backup device executing
RESTORE HEADERONLY
FROM DISK = N'c:\..\phisical path.bck'
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea,
But is it regarded as good programming practise or not..
dev
|||There is nothing inherently "bad" with having several backups in one backup device.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"dev" <anonymous@.discussions.microsoft.com> wrote in message
news:D80398A4-4B44-4365-8C66-2F257114C237@.microsoft.com...
> Thanks Andrea,
> But is it regarded as good programming practise or not..
> dev

background images and external URLs sorting problem

I have created a report which uses an external .bmp accessed through a URL as
the background image of the header (i.e. to brand the report with the company
logo).
this works fine until I use the interactive sort function which causes the
image to disappear. refreshing the report brings back the image, but resets
the sort.
Does anybody know of any way to make the image reappear after using the sort?
thanks in advance!
--
BLVI have exactly the same problem. Have you managed to solve it in the mean
time?
"BLV" wrote:
> I have created a report which uses an external .bmp accessed through a URL as
> the background image of the header (i.e. to brand the report with the company
> logo).
> this works fine until I use the interactive sort function which causes the
> image to disappear. refreshing the report brings back the image, but resets
> the sort.
> Does anybody know of any way to make the image reappear after using the sort?
> thanks in advance!
> --
> BLV

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK = ''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK = ''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
--
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK => ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK => ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
go
Apparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.
|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegro ups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

Back up File Location

I have created a schedule of transaction log backups, 1
every hour from 9am to 8pm with a differential back up
every 4 hours 1pm, 5pm and 9pm and full back ups once a
week. Under the advice of tech support the transaction log
backups should go to an area on another Server rather than
a tape. I want to try and keep the back up sets together,
so i will have a history of Full backup, Differential
backup and Transaction Log backs up in a single set. So i
want to use the same location for the Full and
differential backups. However tech support are going
bananas, saying that i shouldn't be moving so much data
accros the network. They can see that hourly transaction
logs should not be done to tape but are arguing that
weekly full backups should. The databases are not very big
in my eyes), a total of about 600mb and the full backups
run out of hours on a (sunday). As i said before, i would
like to have on backup set, not Full bacckups in one set
on tape and transaction log backups in another set on disk.
Any thoughts will be much appreciated as its me against
the world at the moment...Why not quite simply backup to disk and have those files picked up by your
tape software, like most of us are doing? :-)
For extra safety, you can add a job step which copies the backup file to
another machine directly after the backup is performed. (Personally, I don't
do the backup over the network if I can avoid. I prefer to backup locally
and copy the file after. If database size permits, of course.)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MAT" <anonymous@.discussions.microsoft.com> wrote in message
news:247501c3fc77$17ead6c0$a001280a@.phx.gbl...
> I have created a schedule of transaction log backups, 1
> every hour from 9am to 8pm with a differential back up
> every 4 hours 1pm, 5pm and 9pm and full back ups once a
> week. Under the advice of tech support the transaction log
> backups should go to an area on another Server rather than
> a tape. I want to try and keep the back up sets together,
> so i will have a history of Full backup, Differential
> backup and Transaction Log backs up in a single set. So i
> want to use the same location for the Full and
> differential backups. However tech support are going
> bananas, saying that i shouldn't be moving so much data
> accros the network. They can see that hourly transaction
> logs should not be done to tape but are arguing that
> weekly full backups should. The databases are not very big
> in my eyes), a total of about 600mb and the full backups
> run out of hours on a (sunday). As i said before, i would
> like to have on backup set, not Full bacckups in one set
> on tape and transaction log backups in another set on disk.
> Any thoughts will be much appreciated as its me against
> the world at the moment...
>|||Create a backup LAN between SQL server and backup destination server.
Backup on backup LAN IP address.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"MAT" <anonymous@.discussions.microsoft.com> wrote in message
news:247501c3fc77$17ead6c0$a001280a@.phx.gbl...
> I have created a schedule of transaction log backups, 1
> every hour from 9am to 8pm with a differential back up
> every 4 hours 1pm, 5pm and 9pm and full back ups once a
> week. Under the advice of tech support the transaction log
> backups should go to an area on another Server rather than
> a tape. I want to try and keep the back up sets together,
> so i will have a history of Full backup, Differential
> backup and Transaction Log backs up in a single set. So i
> want to use the same location for the Full and
> differential backups. However tech support are going
> bananas, saying that i shouldn't be moving so much data
> accros the network. They can see that hourly transaction
> logs should not be done to tape but are arguing that
> weekly full backups should. The databases are not very big
> in my eyes), a total of about 600mb and the full backups
> run out of hours on a (sunday). As i said before, i would
> like to have on backup set, not Full bacckups in one set
> on tape and transaction log backups in another set on disk.
> Any thoughts will be much appreciated as its me against
> the world at the moment...
>

avoid using cursors....

I am trying to rewrite a sp that I created years ago to avoid using cursors
so there is no problems with mutiprocessor systems and parallelism.
Simplifying, we have an order table and task table for each order:
CREATE TABLE [TOrders] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NULL ,
CONSTRAINT [PK_TOrders] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [TTasks] (
[OrderID] [int] NOT NULL ,
[ID] [int] NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NOT NULL
CONSTRAINT [PK_TTasks] PRIMARY KEY NONCLUSTERED
(
[OrderID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_TTasks_TOrders] FOREIGN KEY
(
[OrderID]
) REFERENCES [TOrders] (
[ID]
)
) ON [PRIMARY]
GO
The stored procedure under analysis consists of copying (inserting) all the
tasks already existing for an order to another one. Since ID field in TTasks
is not identity, we should retrieve the current maximum ID for the
destination order and continue the insertions from that ID onwards. The SP
as it is now follows:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int,
@.Description varchar(64)
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
-- Cursor to iterate through source taks
DECLARE my_cursor CURSOR LOCAL FOR
SELECT TTasks.Description FROM TTasks
WHERE TTasks.OrderID= @.fromO
ORDER BY TTasks.ID
-- This is a simple iteration to insert tasks but starting at @.i instead
starting at 1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @.Description
WHILE (@.@.FETCH_STATUS <> -1) BEGIN
IF (@.@.FETCH_STATUS <> -2) BEGIN
SET @.i = @.i + 1
INSERT INTO TTasks (OrderID, ID, Description)
VALUES (@.toO, @.i, @.Description)
END
FETCH NEXT FROM my_cursor INTO @.Description
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
What I am trying to do is replace the cursor used there by a single insert
statement such as:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ****, Description FROM TTasks
WHERE TTasks.OrderID = @.fromO
ORDER BY TTasks.ID
GO
What is driving me crazy is how to calculate the field marked with ****
Can someone help me? Is there any way to do it without having ID field in
TTasks being an identity? It cannot be an identity since it must start with
1 for every new order.
Thanks in advance.jagb (jagb@.NOSPAM.com) writes:
> What is driving me crazy is how to calculate the field marked with ****
> Can someone help me? Is there any way to do it without having ID field
> in TTasks being an identity? It cannot be an identity since it must
> start with 1 for every new order.
A very simple-minded solution is to bounce the data over a temp table
with an IDENTITY column.
A more "relational" solution is to add a table of numbers to the database.
This is a one-column table that holds all numbers from 1 up to some limit.
Personally, I sort of favour the temp-table solution, as it is more
robust. You don't risk to run out of numbers. Then again, there is a
performance cost for using an extra table, so for this case I might go
for a table of numbers.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could use a correlated subquery to generate the ranking value within a
SELECT statement like:
INSERT tasks ( ... )
SELECT @.to, ( SELECT COUNT(*)
FROM tasks t2
WHERE t2.orderID = t1.Orderid
AND t2.id <= t1.id )
FROM tasks t1
WHERE t1.orderid = @.p ;
The assumption is that the id values are unique for each Orderid. This is
untested, if you'd like a tested one, please post a few sample data along
with expected results.
Anith|||TOrders (ID, Desc)
1 First Order
2 Second Order
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
After having run the SP to copy tasks from order=1 to order=2 the tasks
table should contain the following values:
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
2 5 a <-- these are the inserted records
2 6 b <-- these are the inserted records
2 7 c <-- these are the inserted records
This is not achieved using your statement wich will incorrectly try to
insert the values:
2 1 a
2 2 b
2 9 c
And will raise a primary key conflict, since the keys (OrderID ID) valued
to (2 1), (2 2) and (2 3) already exist in TTasks.
Note that the (1 9 c) record should be converted to (2 7 c) when
copied along for order 2.
Mhh... it seems that your approach almost hit in the nail... after having
done some tests I have found the solution:
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO,
(SELECT ISNULL(COUNT(*), 0)
FROM dbo.TTasks t2
WHERE (OrderID = t1.OrderID) AND (ID <=
t1.ID)) +
(SELECT ISNULL(MAX(ID), 0)
FROM dbo.TTasks
WHERE (OrderID = @.toO)), Descripcion
FROM dbo.TTasks t1
WHERE (OrderID = @.fromO)
Thanks for your help, Anith.
"Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
> You could use a correlated subquery to generate the ranking value within a
> SELECT statement like:
> INSERT tasks ( ... )
> SELECT @.to, ( SELECT COUNT(*)
> FROM tasks t2
> WHERE t2.orderID = t1.Orderid
> AND t2.id <= t1.id )
> FROM tasks t1
> WHERE t1.orderid = @.p ;
> The assumption is that the id values are unique for each Orderid. This is
> untested, if you'd like a tested one, please post a few sample data along
> with expected results.
> --
> Anith
>|||jagb (jagb@.NOSPAM.com) writes:
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
Beware, though, that nested subqueries in the SELECT list often gives
poor performance. Certinly better than your cursor, it can be considerably
slower than bouncing over a temp table, or using a table of numbers.
Then again, it depends on how many rows you insert at time. If it is
< 100, the difference may not be measurable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You can also use a Table Variable with an IDENTITY column. I like them
better than temp tables for generating row numbers because unlike temp
tables, modifications to table variables don't share the same transaction
space as changes to permanent tables which can reduce the performance hit of
using the extra table. They also reduce recompiles and can reduce
contention on the system table indexes in tempdb.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97428F2528D48Yazorman@.127.0.0.1...
> jagb (jagb@.NOSPAM.com) writes:
> A very simple-minded solution is to bounce the data over a temp table
> with an IDENTITY column.
> A more "relational" solution is to add a table of numbers to the database.
> This is a one-column table that holds all numbers from 1 up to some limit.
> Personally, I sort of favour the temp-table solution, as it is more
> robust. You don't risk to run out of numbers. Then again, there is a
> performance cost for using an extra table, so for this case I might go
> for a table of numbers.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||I think bouncing the values off a temporary object will be faster than a
correlated subquery:
DECLARE @.T TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Description VARCHAR(64) NOT NULL
)
BEGIN TRAN
INSERT @.T (Description)
SELECT Description
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block updates to source order
WHERE OrderID = @.fromO
ORDER BY ID
IF @.@.ROWCOUNT > 0
BEGIN
SELECT @.i = ISNULL(MAX(ID), 0)
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block appending to destination
order
WHERE OrderID = @.to0
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ID + @.i, Description FROM @.T
END
COMMIT TRAN
"jagb" <jagb@.NOSPAM.com> wrote in message
news:esRjDsgEGHA.2704@.TK2MSFTNGP15.phx.gbl...
> TOrders (ID, Desc)
> 1 First Order
> 2 Second Order
>
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> After having run the SP to copy tasks from order=1 to order=2 the tasks
> table should contain the following values:
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> 2 5 a <-- these are the inserted records
> 2 6 b <-- these are the inserted records
> 2 7 c <-- these are the inserted records
> This is not achieved using your statement wich will incorrectly try to
> insert the values:
> 2 1 a
> 2 2 b
> 2 9 c
> And will raise a primary key conflict, since the keys (OrderID ID) valued
> to (2 1), (2 2) and (2 3) already exist in TTasks.
> Note that the (1 9 c) record should be converted to (2 7 c)
> when copied along for order 2.
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
> "Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
> news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user USER created in the server, if I try to make a sql query (select * from tablename) the sql panel writes "select * from USER.tablename" and this causes a error when I try make that select query from a application.
How can I avoid the part USER. in the query?Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user USER created in the server, if I try to make a sql query (select * from tablename) the sql panel writes "select * from USER.tablename" and this causes a error when I try make that s
elect query from a application.
How can I avoid the part USER. in the query?
Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user US
ER created in the server, if I try to make a sql query (select * from tablen
ame) the sql panel writes "select * from USER.tablename" and this causes a e
rror when I try make that s
elect query from a application.
How can I avoid the part USER. in the query?Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

autonumber

sql 2000

how can i set primary key to autonumber?
i created a form with this table,, i get error when trying to insert..
i want "id: column to autogenerate number
...................................... ............................

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'id', table 'Guestbook.dbo.guestbook'; column does not allow nulls. INSERT fails.FYI:

You need to create the Primary Key using IDENTITY [ ( seed , increment )

Tim S

From BOL ( Books On Line)

CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]|||is it possible to alter the table or column to make that changes?|||I have not found a way to alter the Table to make the change.

If use use the Enterprise Manager to change the column it creates a new table and copies the data into it and deletes the old table an renames the new table to match the old table name.

Tim S|||thanks

AutoNum + Exception

Hi all,
I created a table with an autonum field, with a stored procedure to
insert new record to the table. however I found that the autonum will keep
increase when some unique constraints is voliated.
I tried to use Begin Transaction, and rollback when there's error during the
insert statement, but fail to do it. Any solution to solve it? or I have
missed out something?
Thanks a lot for helping.This behavior is by design.
Even if you rollback a transaction, the generated identity will not reset.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<Windy> wrote in message news:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>|||(Windy) writes:
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
There are two main roads to create an surrogate id: 1) Roll your own. 2) Let
the database do it. This is not merely a question of convienence. The
IDENTITY function is designed to be scalable, so that many processes
can insert at the same time without blocking each other. For this reason,
the counter for the IDENTITY is not reset when a transaction rolled back.
Sometimes you have the business requirement that number must be contiguous,
this is typical for accounting applications. In this case, you must roll
your own. But you must then also be prepare to handled a higher degree
of blocking. To wit, process 1 gets a number, and uses it in a longer
transaction. Process 2 also needs a number, but it cannot get one until
Process 1 has completed, for the simple reason that Process 2 cannot
know which is the next number, as that depends on whether Process 1 will
commit or rollback.
The scheme for rolling your own is:
BEGIN TRANSACTION
SELECT @.nextid = coalesce(MAX(id), 0) + 1
FROM tbl WITH (HOLDLOCK, UPDLOCK)
INSERT tbl (id, ...
SELECT @.nextid...
-- More work
COMMIT TRANSACTION
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This behavior is by design, but isn't documented very well in BOL.
It's not a good practice to rely on the contiguity of IDENTITY values for
permanent or otherwise shared tables. A surrogate key value should not add
any meaning to the row for which it is a surrogate. This means that neither
its magnitude nor its relative position with respect to other rows'
surrogate key values should be relied upon in your data model. All that is
important is that that value be different for each row. Also, since
surrogate key values should not add anything, they also cannot be used to
guarantee entity integrity. A unique constraint must also exist whose
definition doesn't include the surrogate key column.
Many outside influences can affect the values generated by IDENTITY. For
example, the administrator may reset the IDENTITY seed if an overflow is
about to occur. The IDENTITY values on a table may need to be changed in
order to facilitate replication or consolidation with other databases.
Also, gaps can occur due to rollbacks or deletes.
I'm not saying that IDENTITY is a bad thing; on the contrary: it's a
valuable tool, but it's important that it be used correctly.
I have used the IDENTITY property on table variables and local temporary
tables to facilitate sequencing and ordering, but that occurs entirely
within the body of a procedure or trigger, and because the objects are local
to the connection, there cannot be any any interaction with other
transactions.
<Windy> wrote in message news:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>|||The December 2005 issue of SQL Server Magazine has an article by Itzik
Ben-Gan on creating a custom identity generating stored procedure.
http://www.windowsitpro.com/Article...8165/48165.html
You'll need a subscription to access the full article.
"Windy" wrote:

> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during t
he
> insert statement, but fail to do it. Any solution to solve it? or I have
> missed out something?
> Thanks a lot for helping.
>
>|||Many thanks to all of you guys.
<Windy> glsD:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>