Bad Syntax

I just cannot get the syntax right for this: (The problem is in my TSart and TEnd)

Dim SQL As String = "Select DateEntered FROM tblTasks Where [DateEntered] Between " '" & TStart & "'" And "'" & TEnd & "'" And [ID] = " & _
IDSent

Thank you for any help,Thanks anyway, I got it. Long Day.

Bad Stored procedure

I get this error "Incorrect syntax near @.TopNews"
Here is my SP:

CREATE PROCEDURE sp_TopNews
@.TopNews int
AS
SELECT TOP @.TopNews [Title], [Content] FROM [Documents]
WHERE [DatePublication] >= GETDATE() AND [Type] = "News"
AND [DateArchived] = NULL

It's my first SP so it's probably a stupid error, can anyone help me ?You cannot use a variable for the number in TOP. You could use dynamic SQL.|||Based on http://www.sqlteam.com/item.asp?ItemID=233
Try this :

CREATE PROCEDURE sp_TopNews
 @.TopNews int
AS
SET rowcount @.TopNews
SELECT [Title], [Content]
 FROM [Documents]
 WHERE [DatePublication] >= GETDATE() AND
 [Type] = "News" AND
 [DateArchived] = NULL
SET rowcount 0
GO

|||Of course, without an order by statement, this is worthless...|||Thanks that worked :)|||indeed

Backups over a network

I am using the following syntax in sql 2000 in order to transfer the backup across the network to a backup machine:

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'

The problem I am experiencing is that when I run this script, I get the response:

Access is denied.
0 file(s) copied

Can anyone tell me what I am doing wrong?Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

Still, don't you think it's a security thing?|||Originally posted by Brett Kaiser
Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

he is backing up locally and then trying to copy it over the network .. Brett

read the post again

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'|||Ok, ive given up and having sql do the move for me. Not exactly sure whats causing the problem, but I have worked around it. I have just written a bat file that copies over a mapped drive that is run thru the schedule tasks interface. Not as clean as I would like, but it works.

Thanks again guys

Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME
+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
--
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
Larry
Nope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNA ME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

backup to device

I am using a device to backup my user database for both data and the transaction log. I want to automate this process and I understand the syntax for the backup of log and data. I want to know what the syntax is for having the complete backup overwrite the existing backup in the device after a number of backups have occured. I can't find anything in the users manual for this. I also want to have my transaction log backups be overwritten periodically. With my current setup the backup device grows and grows
For example
after the third data backup I want the first backup to be overwritten so the device only contains that last three backups. I will backup the transaction log 2x per day, and I want to keep that last 6 transaction log backups to be stored on the device and then the oldest transaction log backup in the device will be overwritten.
thanks>after the third data backup I want the first backup to be overwritten so
the device only contains that last three backups.
I don't think this is doable. When you do backup you use WITH INIT or WITH
NOINIT to tell backup to orverwrite or append to the backup device. No way
you can tell it to purge the first backup set (if there are 3 exist) and
append a new backup set to the backup device. Similar to the log backup.
One thing you can do is you have 3 backup devices for each day. Lets say
you have BACKUP1, BACKUP2, BACKUP3. Do a full backup and 2 log backups
(appended) to each backup device every day. Schedule a job to run full
backup and another job to do log backup. Before each backup do an IF..ELSE
to find out what backup device was used the day before so your backup will
know what backup device to use today.
hth,
"Stephen Harris" <anonymous@.discussions.microsoft.com> wrote in message
news:2C185839-AF58-4269-B4B3-EA0335036FB3@.microsoft.com...
> I am using a device to backup my user database for both data and the
transaction log. I want to automate this process and I understand the
syntax for the backup of log and data. I want to know what the syntax is
for having the complete backup overwrite the existing backup in the device
after a number of backups have occured. I can't find anything in the users
manual for this. I also want to have my transaction log backups be
overwritten periodically. With my current setup the backup device grows and
grows.
> For example,
> after the third data backup I want the first backup to be overwritten so
the device only contains that last three backups. I will backup the
transaction log 2x per day, and I want to keep that last 6 transaction log
backups to be stored on the device and then the oldest transaction log
backup in the device will be overwritten.
> thanks