Automate a sql server restore

Is there a way to automate a sql server restore? I have a db that I need to include in an installation. I could use generate script but there is info in the db that needs to be copied too. What is the best way to do this?
Thanx
weisenbrHow much data do you need to add? If it's a minimal amount, it can be done through scripting. I would recommend the IbuySpy portal db scripts as a way of seeing how this is done. Probably the Portal Starter kit has this as well, but I haven't looked into that yet. But download one of those, and check out their scripts and you'll see how they can insert data.

If it's a lot of data, you could also do automated backups (using EM). I haven't done this in an installation, but you could also look into attaching a database (sp_attach_single_file_db). I would only recommend this route if the folks installing the db are sql server savvy or at least have a good knowledge of IT methodology.

Personally I think scripting your database tables, and even initial data is the way to go. But I'm not sure if you're talking small amounts of data or large amounts.

Automate a restore through a backup

Hi all,
Regards SQL Server 2000.
I have a situation whereby the client takes a nightly backup of his
databases. The backups are placed on a network share. He has a laptop where
he has sql server installed and wants to update the laptop's databases from
the lastest backup of the day before.
Is there a way to automate (within enterprice manager or t-sql) the
restoration of a database through a backup file?
Thanks,
Ivan> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
Sure, you can execute a T-SQL restore script. You may need to specify the
MOVE option if you need to restore to different drives/folders than the
source database:
RESTORE DATABASE MyDatabase
FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
The restore runs in the OS security context of the SQL Server service
account when performed by a sysadmin role member so the service account will
need read permissions to the network backup share. Also, you can include
the restore scripts in a SQL Agent job to facilitate execution, either
scheduled or manual.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Regards SQL Server 2000.
> I have a situation whereby the client takes a nightly backup of his
> databases. The backups are placed on a network share. He has a laptop
> where he has sql server installed and wants to update the laptop's
> databases from the lastest backup of the day before.
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
> Thanks,
> Ivan
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
He may also want to look into straight log-shipping.
There's scripts for 'rolling your own'.
Rather than restore the full backup every night, he can restore just the
logs and put the DB into read-only mode if that'll work for him.
Even if he doesn't go that far, he can get "smarter" possibly and look in
the MSDB for the last backup set and automatically load that one.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
That's a great idea. One problem is that the backup job creates the backup
files with a timestamp in the name. Is there a way to exclude this or at
least get the latest backup copy automatically?
Thanks,
Ivan|||On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
> That's a great idea. One problem is that the backup job creates the backup
> files with a timestamp in the name. Is there a way to exclude this or at
> least get the latest backup copy automatically?
> Thanks,
> Ivan- Hide quoted text -
> - Show quoted text -
You can rename your latest backup file to a constant file name and
restore it from this.
If you don't want to change the name of the backup file use
xp_cmdshell 'dir ' and put into a table and parse to get the name of
the latest backup file|||"M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> You can rename your latest backup file to a constant file name and
> restore it from this.
> If you don't want to change the name of the backup file use
> xp_cmdshell 'dir ' and put into a table and parse to get the name of
> the latest backup file
>
And how does one places the output into the table?
Ivan|||> And how does one places the output into the table?
You can use INSERT...EXEC. For example:
CREATE TABLE #FileList(output_line varchar(8000))
INSERT INTO #FileList
EXEC master..xp_cmdshell 'DIR C:\Backups'
However, I think parsing the output is a bit of a kludge. You might instead
consider using a VBScript that you can include as an ActiveX script job step
or a stand-alone vbs file. Below is a VBScript example you can tweak for
your needs.
'Restore from latest backup
Option Explicit
Const BackupFolder = "C:\Backups"
Const ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;Integrated Security=SSPI"
Const RestoreScriptTemplate = "RESTORE DATABASE MyDatabase FROM
DISK='$(BackupFilePath)'"
Dim oFSO, BackupFilePath
Set oFSO = CreateObject("Scripting.FileSystemObject")
BackupFilePath = GetLatestBackupFile()
RestoreDatabase ConnectionString, BackupFilePath
Function GetLatestBackupFile()
Dim oFolder, oFile, LatestFilePath, LatestFileDateCreated
Set oFolder = oFSO.GetFolder(BackupFolder)
For Each oFile In oFolder.Files
If oFile.DateCreated > LatestFileDateCreated Then
LatestFileDateCreated = oFile.DateCreated
LatestFilePath = oFile.Path
End If
Next
GetLatestBackupFile = LatestFilePath
End Function
Sub RestoreDatabase(ConnectionString, BackupFilePath)
Dim RestoreScript, connection
RestoreScript = Replace(RestoreScriptTemplate, "$(BackupFilePath)",
BackupFilePath)
Set connection = CreateObject("ADODB.Connection")
connection.Open ConnectionString
connection.Execute RestoreScript
connection.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%239dlo%23qdHHA.4872@.TK2MSFTNGP03.phx.gbl...
> "M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
> news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
> And how does one places the output into the table?
> Ivan
>

Automate a restore through a backup

Hi all,
Regards SQL Server 2000.
I have a situation whereby the client takes a nightly backup of his
databases. The backups are placed on a network share. He has a laptop where
he has sql server installed and wants to update the laptop's databases from
the lastest backup of the day before.
Is there a way to automate (within enterprice manager or t-sql) the
restoration of a database through a backup file?
Thanks,
Ivan
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
Sure, you can execute a T-SQL restore script. You may need to specify the
MOVE option if you need to restore to different drives/folders than the
source database:
RESTORE DATABASE MyDatabase
FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
The restore runs in the OS security context of the SQL Server service
account when performed by a sysadmin role member so the service account will
need read permissions to the network backup share. Also, you can include
the restore scripts in a SQL Agent job to facilitate execution, either
scheduled or manual.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Regards SQL Server 2000.
> I have a situation whereby the client takes a nightly backup of his
> databases. The backups are placed on a network share. He has a laptop
> where he has sql server installed and wants to update the laptop's
> databases from the lastest backup of the day before.
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
> Thanks,
> Ivan
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
He may also want to look into straight log-shipping.
There's scripts for 'rolling your own'.
Rather than restore the full backup every night, he can restore just the
logs and put the DB into read-only mode if that'll work for him.
Even if he doesn't go that far, he can get "smarter" possibly and look in
the MSDB for the last backup set and automatically load that one.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
That's a great idea. One problem is that the backup job creates the backup
files with a timestamp in the name. Is there a way to exclude this or at
least get the latest backup copy automatically?
Thanks,
Ivan
|||On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im Newsbeitragnews:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>
>
>
>
>
>
>
>
> That's a great idea. One problem is that the backup job creates the backup
> files with a timestamp in the name. Is there a way to exclude this or at
> least get the latest backup copy automatically?
> Thanks,
> Ivan- Hide quoted text -
> - Show quoted text -
You can rename your latest backup file to a constant file name and
restore it from this.
If you don't want to change the name of the backup file use
xp_cmdshell 'dir ' and put into a table and parse to get the name of
the latest backup file
|||"M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
news:1175684363.282267.133330@.e65g2000hsc.googlegr oups.com...
> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> You can rename your latest backup file to a constant file name and
> restore it from this.
> If you don't want to change the name of the backup file use
> xp_cmdshell 'dir ' and put into a table and parse to get the name of
> the latest backup file
>
And how does one places the output into the table?
Ivan
|||> And how does one places the output into the table?
You can use INSERT...EXEC. For example:
CREATE TABLE #FileList(output_line varchar(8000))
INSERT INTO #FileList
EXEC master..xp_cmdshell 'DIR C:\Backups'
However, I think parsing the output is a bit of a kludge. You might instead
consider using a VBScript that you can include as an ActiveX script job step
or a stand-alone vbs file. Below is a VBScript example you can tweak for
your needs.
'Restore from latest backup
Option Explicit
Const BackupFolder = "C:\Backups"
Const ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;Integrated Security=SSPI"
Const RestoreScriptTemplate = "RESTORE DATABASE MyDatabase FROM
DISK='$(BackupFilePath)'"
Dim oFSO, BackupFilePath
Set oFSO = CreateObject("Scripting.FileSystemObject")
BackupFilePath = GetLatestBackupFile()
RestoreDatabase ConnectionString, BackupFilePath
Function GetLatestBackupFile()
Dim oFolder, oFile, LatestFilePath, LatestFileDateCreated
Set oFolder = oFSO.GetFolder(BackupFolder)
For Each oFile In oFolder.Files
If oFile.DateCreated > LatestFileDateCreated Then
LatestFileDateCreated = oFile.DateCreated
LatestFilePath = oFile.Path
End If
Next
GetLatestBackupFile = LatestFilePath
End Function
Sub RestoreDatabase(ConnectionString, BackupFilePath)
Dim RestoreScript, connection
RestoreScript = Replace(RestoreScriptTemplate, "$(BackupFilePath)",
BackupFilePath)
Set connection = CreateObject("ADODB.Connection")
connection.Open ConnectionString
connection.Execute RestoreScript
connection.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%239dlo%23qdHHA.4872@.TK2MSFTNGP03.phx.gbl...
> "M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
> news:1175684363.282267.133330@.e65g2000hsc.googlegr oups.com...
> And how does one places the output into the table?
> Ivan
>

Automate a restore through a backup

Hi all,
Regards SQL Server 2000.
I have a situation whereby the client takes a nightly backup of his
databases. The backups are placed on a network share. He has a laptop where
he has sql server installed and wants to update the laptop's databases from
the lastest backup of the day before.
Is there a way to automate (within enterprice manager or t-sql) the
restoration of a database through a backup file?
Thanks,
Ivan> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
Sure, you can execute a T-SQL restore script. You may need to specify the
MOVE option if you need to restore to different drives/folders than the
source database:
RESTORE DATABASE MyDatabase
FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
The restore runs in the OS security context of the SQL Server service
account when performed by a sysadmin role member so the service account will
need read permissions to the network backup share. Also, you can include
the restore scripts in a SQL Agent job to facilitate execution, either
scheduled or manual.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Regards SQL Server 2000.
> I have a situation whereby the client takes a nightly backup of his
> databases. The backups are placed on a network share. He has a laptop
> where he has sql server installed and wants to update the laptop's
> databases from the lastest backup of the day before.
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
> Thanks,
> Ivan
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>> Is there a way to automate (within enterprice manager or t-sql) the
>> restoration of a database through a backup file?
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
He may also want to look into straight log-shipping.
There's scripts for 'rolling your own'.
Rather than restore the full backup every night, he can restore just the
logs and put the DB into read-only mode if that'll work for him.
Even if he doesn't go that far, he can get "smarter" possibly and look in
the MSDB for the last backup set and automatically load that one.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> Regards SQL Server 2000.
>> I have a situation whereby the client takes a nightly backup of his
>> databases. The backups are placed on a network share. He has a laptop
>> where he has sql server installed and wants to update the laptop's
>> databases from the lastest backup of the day before.
>> Is there a way to automate (within enterprice manager or t-sql) the
>> restoration of a database through a backup file?
>> Thanks,
>> Ivan
>>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>> Is there a way to automate (within enterprice manager or t-sql) the
>> restoration of a database through a backup file?
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> Regards SQL Server 2000.
>> I have a situation whereby the client takes a nightly backup of his
>> databases. The backups are placed on a network share. He has a laptop
>> where he has sql server installed and wants to update the laptop's
>> databases from the lastest backup of the day before.
>> Is there a way to automate (within enterprice manager or t-sql) the
>> restoration of a database through a backup file?
>> Thanks,
>> Ivan
>>
>
That's a great idea. One problem is that the backup job creates the backup
files with a timestamp in the name. Is there a way to exclude this or at
least get the latest backup copy automatically?
Thanks,
Ivan|||On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im Newsbeitragnews:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>
>
> >> Is there a way to automate (within enterprice manager or t-sql) the
> >> restoration of a database through a backup file?
> > Sure, you can execute a T-SQL restore script. You may need to specify the
> > MOVE option if you need to restore to different drives/folders than the
> > source database:
> > RESTORE DATABASE MyDatabase
> > FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> > WITH
> > MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> > MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> > The restore runs in the OS security context of the SQL Server service
> > account when performed by a sysadmin role member so the service account
> > will need read permissions to the network backup share. Also, you can
> > include the restore scripts in a SQL Agent job to facilitate execution,
> > either scheduled or manual.
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Ivan Debono" <ivanm...@.hotmail.com> wrote in message
> >news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> >> Hi all,
> >> Regards SQL Server 2000.
> >> I have a situation whereby the client takes a nightly backup of his
> >> databases. The backups are placed on a network share. He has a laptop
> >> where he has sql server installed and wants to update the laptop's
> >> databases from the lastest backup of the day before.
> >> Is there a way to automate (within enterprice manager or t-sql) the
> >> restoration of a database through a backup file?
> >> Thanks,
> >> Ivan
> That's a great idea. One problem is that the backup job creates the backup
> files with a timestamp in the name. Is there a way to exclude this or at
> least get the latest backup copy automatically?
> Thanks,
> Ivan- Hide quoted text -
> - Show quoted text -
You can rename your latest backup file to a constant file name and
restore it from this.
If you don't want to change the name of the backup file use
xp_cmdshell 'dir ' and put into a table and parse to get the name of
the latest backup file|||"M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
>> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im
>> Newsbeitragnews:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>>
>>
>> >> Is there a way to automate (within enterprice manager or t-sql) the
>> >> restoration of a database through a backup file?
>> > Sure, you can execute a T-SQL restore script. You may need to specify
>> > the
>> > MOVE option if you need to restore to different drives/folders than the
>> > source database:
>> > RESTORE DATABASE MyDatabase
>> > FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
>> > WITH
>> > MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
>> > MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
>> > The restore runs in the OS security context of the SQL Server service
>> > account when performed by a sysadmin role member so the service account
>> > will need read permissions to the network backup share. Also, you can
>> > include the restore scripts in a SQL Agent job to facilitate execution,
>> > either scheduled or manual.
>> > --
>> > Hope this helps.
>> > Dan Guzman
>> > SQL Server MVP
>> > "Ivan Debono" <ivanm...@.hotmail.com> wrote in message
>> >news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> >> Hi all,
>> >> Regards SQL Server 2000.
>> >> I have a situation whereby the client takes a nightly backup of his
>> >> databases. The backups are placed on a network share. He has a laptop
>> >> where he has sql server installed and wants to update the laptop's
>> >> databases from the lastest backup of the day before.
>> >> Is there a way to automate (within enterprice manager or t-sql) the
>> >> restoration of a database through a backup file?
>> >> Thanks,
>> >> Ivan
>> That's a great idea. One problem is that the backup job creates the
>> backup
>> files with a timestamp in the name. Is there a way to exclude this or at
>> least get the latest backup copy automatically?
>> Thanks,
>> Ivan- Hide quoted text -
>> - Show quoted text -
> You can rename your latest backup file to a constant file name and
> restore it from this.
> If you don't want to change the name of the backup file use
> xp_cmdshell 'dir ' and put into a table and parse to get the name of
> the latest backup file
>
And how does one places the output into the table?
Ivan|||> And how does one places the output into the table?
You can use INSERT...EXEC. For example:
CREATE TABLE #FileList(output_line varchar(8000))
INSERT INTO #FileList
EXEC master..xp_cmdshell 'DIR C:\Backups'
However, I think parsing the output is a bit of a kludge. You might instead
consider using a VBScript that you can include as an ActiveX script job step
or a stand-alone vbs file. Below is a VBScript example you can tweak for
your needs.
'Restore from latest backup
Option Explicit
Const BackupFolder = "C:\Backups"
Const ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;Integrated Security=SSPI"
Const RestoreScriptTemplate = "RESTORE DATABASE MyDatabase FROM
DISK='$(BackupFilePath)'"
Dim oFSO, BackupFilePath
Set oFSO = CreateObject("Scripting.FileSystemObject")
BackupFilePath = GetLatestBackupFile()
RestoreDatabase ConnectionString, BackupFilePath
Function GetLatestBackupFile()
Dim oFolder, oFile, LatestFilePath, LatestFileDateCreated
Set oFolder = oFSO.GetFolder(BackupFolder)
For Each oFile In oFolder.Files
If oFile.DateCreated > LatestFileDateCreated Then
LatestFileDateCreated = oFile.DateCreated
LatestFilePath = oFile.Path
End If
Next
GetLatestBackupFile = LatestFilePath
End Function
Sub RestoreDatabase(ConnectionString, BackupFilePath)
Dim RestoreScript, connection
RestoreScript = Replace(RestoreScriptTemplate, "$(BackupFilePath)",
BackupFilePath)
Set connection = CreateObject("ADODB.Connection")
connection.Open ConnectionString
connection.Execute RestoreScript
connection.Close
End Sub
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%239dlo%23qdHHA.4872@.TK2MSFTNGP03.phx.gbl...
> "M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
> news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
>> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
>> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im
>> Newsbeitragnews:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>>
>>
>> >> Is there a way to automate (within enterprice manager or t-sql) the
>> >> restoration of a database through a backup file?
>> > Sure, you can execute a T-SQL restore script. You may need to specify
>> > the
>> > MOVE option if you need to restore to different drives/folders than
>> > the
>> > source database:
>> > RESTORE DATABASE MyDatabase
>> > FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
>> > WITH
>> > MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
>> > MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
>> > The restore runs in the OS security context of the SQL Server service
>> > account when performed by a sysadmin role member so the service
>> > account
>> > will need read permissions to the network backup share. Also, you can
>> > include the restore scripts in a SQL Agent job to facilitate
>> > execution,
>> > either scheduled or manual.
>> > --
>> > Hope this helps.
>> > Dan Guzman
>> > SQL Server MVP
>> > "Ivan Debono" <ivanm...@.hotmail.com> wrote in message
>> >news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> >> Hi all,
>> >> Regards SQL Server 2000.
>> >> I have a situation whereby the client takes a nightly backup of his
>> >> databases. The backups are placed on a network share. He has a laptop
>> >> where he has sql server installed and wants to update the laptop's
>> >> databases from the lastest backup of the day before.
>> >> Is there a way to automate (within enterprice manager or t-sql) the
>> >> restoration of a database through a backup file?
>> >> Thanks,
>> >> Ivan
>> That's a great idea. One problem is that the backup job creates the
>> backup
>> files with a timestamp in the name. Is there a way to exclude this or at
>> least get the latest backup copy automatically?
>> Thanks,
>> Ivan- Hide quoted text -
>> - Show quoted text -
>> You can rename your latest backup file to a constant file name and
>> restore it from this.
>> If you don't want to change the name of the backup file use
>> xp_cmdshell 'dir ' and put into a table and parse to get the name of
>> the latest backup file
> And how does one places the output into the table?
> Ivan
>

Automate a nightly query and email results...

Hi,
I was wondering if someone could help with my latest project.
I need to run a nightly query on our MS SQL 2000 DB, have the results saved
to a text file and then email the file to someone. I have no idea were to
begin with this one...help?
Thanks,
MitchYou can create a Data Transformation Services package to create the file
based on the query and then email the file. Schedule the package to run ever
y
night.
Data Transformation Services (DTS) in
Microsoft SQL Server 2000
http://msdn.microsoft.com/sql/sqlwa...ts_overview.asp
Data Transformation Services
http://msdn.microsoft.com/library/d...asp?frame=true
AMB
"mitch" wrote:

> Hi,
> I was wondering if someone could help with my latest project.
> I need to run a nightly query on our MS SQL 2000 DB, have the results save
d
> to a text file and then email the file to someone. I have no idea were to
> begin with this one...help?
> Thanks,
> Mitch
>
>|||I would do this using a DTS package or using BCP.
For example, let's go with a DTS package:
In the package, you will add an SQL Server source, and flat file destination
connections. You add a data pump task that connects these two connections,
and pumps out your query results to the flat file.
Then the next step will send an email by specifying the file name as the
attachment.
For sending emails, I use xp_smtp_sendmail, which is a free download from
http://sqldev.net
You could also use SQL Mail. See SQL Server Books Online for more
information.
if you are not familiar with DTS, start with Books Online, and play around
with it from the DTS designer in Enterprise manager. For DTS info, checkout
sqldts.com.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"mitch" <mitch_001@.REMOVEyahoo.com> wrote in message
news:eUiUKWSnFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I was wondering if someone could help with my latest project.
> I need to run a nightly query on our MS SQL 2000 DB, have the results
> saved to a text file and then email the file to someone. I have no idea
> were to begin with this one...help?
> Thanks,
> Mitch
>|||Hi,
If u wanted to transfer all data to a text file then use bcp utility
ex:
exec master..xp_cmdshell 'bcp epinav.dbo.dlvmode out
D:\scriptrecordtable\dlvmode.bcp -n -"EPIOLAPP428G" -U -P'
epiolapp428g is the servername
for more help read books online just type BCP
if u wanted selected data then create a view and tranfer data from
view.
with the same as above.
for sending mail i refer to vyas solution.
to copy data from text file to ur sql table
use this
exec master..xp_cmdshell 'bcp epinav.dbo.custtable in
E:\RepScript\custtable.bcp -n -"aicml370" -U -P'
hope this help
from
killer|||Thanks everyone!!!
I started playing around with DTS and was able to setup a query and save the
info to a text file, but now I have 2 more question.
The information is saved in the text file row by row, BUT it is inserting a
space between each row, is there some way to remove the space?
Also, when a record is submitted it has to follow a YYYYMMDD format so I
placed a hidden text field in the form like this one:
<input name="source_date" type="hidden" id="source_date" value="<%
response.write YEAR(Date()) & _
Pd(Month(date()),2) & _
Pd(DAY(date()),2)
%>">
I am having some trouble in the query that runs in DTS to automatically
capture today's date and use it in the WHERE clause:
where [request_info_form].[source_date]='20050113'
Can you guys help me think of a way?
Thank you very much for all the help!
Mitch
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%238YBLiSnFHA.2156@.TK2MSFTNGP14.phx.gbl...
>I would do this using a DTS package or using BCP.
> For example, let's go with a DTS package:
> In the package, you will add an SQL Server source, and flat file
> destination connections. You add a data pump task that connects these two
> connections, and pumps out your query results to the flat file.
> Then the next step will send an email by specifying the file name as the
> attachment.
> For sending emails, I use xp_smtp_sendmail, which is a free download from
> http://sqldev.net
> You could also use SQL Mail. See SQL Server Books Online for more
> information.
> if you are not familiar with DTS, start with Books Online, and play around
> with it from the DTS designer in Enterprise manager. For DTS info,
> checkout sqldts.com.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "mitch" <mitch_001@.REMOVEyahoo.com> wrote in message
> news:eUiUKWSnFHA.576@.TK2MSFTNGP15.phx.gbl...
>|||Something like this should work...
select @.yourdate = (select convert(CHAR(8),getdate(),112))
This puts things in yyyymmdd format.|||Works perfectly!!! Thanks!
<unc27932@.yahoo.com> wrote in message
news:1123689630.540990.188690@.z14g2000cwz.googlegroups.com...
> Something like this should work...
> select @.yourdate = (select convert(CHAR(8),getdate(),112))
> This puts things in yyyymmdd format.
>

Automate a nightly query and email results...

Hi,
I was wondering if someone could help with my latest project.
I need to run a nightly query on our MS SQL 2000 DB, have the results saved
to a text file and then email the file to someone. I have no idea were to
begin with this one...help?
Thanks,
Mitch
You can create a Data Transformation Services package to create the file
based on the query and then email the file. Schedule the package to run every
night.
Data Transformation Services (DTS) in
Microsoft SQL Server 2000
http://msdn.microsoft.com/sql/sqlwar...s_overview.asp
Data Transformation Services
http://msdn.microsoft.com/library/de...asp?frame=true
AMB
"mitch" wrote:

> Hi,
> I was wondering if someone could help with my latest project.
> I need to run a nightly query on our MS SQL 2000 DB, have the results saved
> to a text file and then email the file to someone. I have no idea were to
> begin with this one...help?
> Thanks,
> Mitch
>
>
|||I would do this using a DTS package or using BCP.
For example, let's go with a DTS package:
In the package, you will add an SQL Server source, and flat file destination
connections. You add a data pump task that connects these two connections,
and pumps out your query results to the flat file.
Then the next step will send an email by specifying the file name as the
attachment.
For sending emails, I use xp_smtp_sendmail, which is a free download from
http://sqldev.net
You could also use SQL Mail. See SQL Server Books Online for more
information.
if you are not familiar with DTS, start with Books Online, and play around
with it from the DTS designer in Enterprise manager. For DTS info, checkout
sqldts.com.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"mitch" <mitch_001@.REMOVEyahoo.com> wrote in message
news:eUiUKWSnFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I was wondering if someone could help with my latest project.
> I need to run a nightly query on our MS SQL 2000 DB, have the results
> saved to a text file and then email the file to someone. I have no idea
> were to begin with this one...help?
> Thanks,
> Mitch
>
|||Hi,
If u wanted to transfer all data to a text file then use bcp utility
ex:
exec master..xp_cmdshell 'bcp epinav.dbo.dlvmode out
D:\scriptrecordtable\dlvmode.bcp -n -"EPIOLAPP428G" -U -P'
epiolapp428g is the servername
for more help read books online just type BCP
if u wanted selected data then create a view and tranfer data from
view.
with the same as above.
for sending mail i refer to vyas solution.
to copy data from text file to ur sql table
use this
exec master..xp_cmdshell 'bcp epinav.dbo.custtable in
E:\RepScript\custtable.bcp -n -"aicml370" -U -P'
hope this help
from
killer
|||Thanks everyone!!!
I started playing around with DTS and was able to setup a query and save the
info to a text file, but now I have 2 more question.
The information is saved in the text file row by row, BUT it is inserting a
space between each row, is there some way to remove the space?
Also, when a record is submitted it has to follow a YYYYMMDD format so I
placed a hidden text field in the form like this one:
<input name="source_date" type="hidden" id="source_date" value="<%
response.write YEAR(Date()) & _
Pd(Month(date()),2) & _
Pd(DAY(date()),2)
%>">
I am having some trouble in the query that runs in DTS to automatically
capture today's date and use it in the WHERE clause:
where [request_info_form].[source_date]='20050113'
Can you guys help me think of a way?
Thank you very much for all the help!
Mitch
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%238YBLiSnFHA.2156@.TK2MSFTNGP14.phx.gbl...
>I would do this using a DTS package or using BCP.
> For example, let's go with a DTS package:
> In the package, you will add an SQL Server source, and flat file
> destination connections. You add a data pump task that connects these two
> connections, and pumps out your query results to the flat file.
> Then the next step will send an email by specifying the file name as the
> attachment.
> For sending emails, I use xp_smtp_sendmail, which is a free download from
> http://sqldev.net
> You could also use SQL Mail. See SQL Server Books Online for more
> information.
> if you are not familiar with DTS, start with Books Online, and play around
> with it from the DTS designer in Enterprise manager. For DTS info,
> checkout sqldts.com.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "mitch" <mitch_001@.REMOVEyahoo.com> wrote in message
> news:eUiUKWSnFHA.576@.TK2MSFTNGP15.phx.gbl...
>
|||Something like this should work...
select @.yourdate = (select convert(CHAR(8),getdate(),112))
This puts things in yyyymmdd format.
|||Works perfectly!!! Thanks!
<unc27932@.yahoo.com> wrote in message
news:1123689630.540990.188690@.z14g2000cwz.googlegr oups.com...
> Something like this should work...
> select @.yourdate = (select convert(CHAR(8),getdate(),112))
> This puts things in yyyymmdd format.
>

Automate a nightly query and email results...

Hi,
I was wondering if someone could help with my latest project.
I need to run a nightly query on our MS SQL 2000 DB, have the results saved
to a text file and then email the file to someone. I have no idea were to
begin with this one...help?
Thanks,
MitchYou can create a Data Transformation Services package to create the file
based on the query and then email the file. Schedule the package to run every
night.
Data Transformation Services (DTS) in
Microsoft SQL Server 2000
http://msdn.microsoft.com/sql/sqlwarehouse/dts/default.aspx?pull=/library/en-us/dnsql2k/html/dts_overview.asp
Data Transformation Services
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_basic_5zg3.asp?frame=true
AMB
"mitch" wrote:
> Hi,
> I was wondering if someone could help with my latest project.
> I need to run a nightly query on our MS SQL 2000 DB, have the results saved
> to a text file and then email the file to someone. I have no idea were to
> begin with this one...help?
> Thanks,
> Mitch
>
>|||I would do this using a DTS package or using BCP.
For example, let's go with a DTS package:
In the package, you will add an SQL Server source, and flat file destination
connections. You add a data pump task that connects these two connections,
and pumps out your query results to the flat file.
Then the next step will send an email by specifying the file name as the
attachment.
For sending emails, I use xp_smtp_sendmail, which is a free download from
http://sqldev.net
You could also use SQL Mail. See SQL Server Books Online for more
information.
if you are not familiar with DTS, start with Books Online, and play around
with it from the DTS designer in Enterprise manager. For DTS info, checkout
sqldts.com.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"mitch" <mitch_001@.REMOVEyahoo.com> wrote in message
news:eUiUKWSnFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I was wondering if someone could help with my latest project.
> I need to run a nightly query on our MS SQL 2000 DB, have the results
> saved to a text file and then email the file to someone. I have no idea
> were to begin with this one...help?
> Thanks,
> Mitch
>|||Hi,
If u wanted to transfer all data to a text file then use bcp utility
ex:
exec master..xp_cmdshell 'bcp epinav.dbo.dlvmode out
D:\scriptrecordtable\dlvmode.bcp -n -"EPIOLAPP428G" -U -P'
epiolapp428g is the servername
for more help read books online just type BCP
if u wanted selected data then create a view and tranfer data from
view.
with the same as above.
for sending mail i refer to vyas solution.
to copy data from text file to ur sql table
use this
exec master..xp_cmdshell 'bcp epinav.dbo.custtable in
E:\RepScript\custtable.bcp -n -"aicml370" -U -P'
hope this help
from
killer|||Thanks everyone!!!
I started playing around with DTS and was able to setup a query and save the
info to a text file, but now I have 2 more question.
The information is saved in the text file row by row, BUT it is inserting a
space between each row, is there some way to remove the space?
Also, when a record is submitted it has to follow a YYYYMMDD format so I
placed a hidden text field in the form like this one:
<input name="source_date" type="hidden" id="source_date" value="<%
response.write YEAR(Date()) & _
Pd(Month(date()),2) & _
Pd(DAY(date()),2)
%>">
I am having some trouble in the query that runs in DTS to automatically
capture today's date and use it in the WHERE clause:
where [request_info_form].[source_date]='20050113'
Can you guys help me think of a way?
Thank you very much for all the help!
Mitch
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%238YBLiSnFHA.2156@.TK2MSFTNGP14.phx.gbl...
>I would do this using a DTS package or using BCP.
> For example, let's go with a DTS package:
> In the package, you will add an SQL Server source, and flat file
> destination connections. You add a data pump task that connects these two
> connections, and pumps out your query results to the flat file.
> Then the next step will send an email by specifying the file name as the
> attachment.
> For sending emails, I use xp_smtp_sendmail, which is a free download from
> http://sqldev.net
> You could also use SQL Mail. See SQL Server Books Online for more
> information.
> if you are not familiar with DTS, start with Books Online, and play around
> with it from the DTS designer in Enterprise manager. For DTS info,
> checkout sqldts.com.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "mitch" <mitch_001@.REMOVEyahoo.com> wrote in message
> news:eUiUKWSnFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I was wondering if someone could help with my latest project.
>> I need to run a nightly query on our MS SQL 2000 DB, have the results
>> saved to a text file and then email the file to someone. I have no idea
>> were to begin with this one...help?
>> Thanks,
>> Mitch
>|||Something like this should work...
select @.yourdate = (select convert(CHAR(8),getdate(),112))
This puts things in yyyymmdd format.|||Works perfectly!!! Thanks!
<unc27932@.yahoo.com> wrote in message
news:1123689630.540990.188690@.z14g2000cwz.googlegroups.com...
> Something like this should work...
> select @.yourdate = (select convert(CHAR(8),getdate(),112))
> This puts things in yyyymmdd format.
>

Automate "Week Ending" Date

How do i automate, by stored procedure, generating the "week ending" date using a field data.

------------------

CREATE TABLE [dbo].[t_Work_Hours] (
[WorkHoursID_PK] [int] IDENTITY (1, 1) NOT NULL ,
[PeopleID_FK] [int] NOT NULL ,
[JobID_FK] [int] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Title] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeekEnding] [datetime] NULL ,
CONSTRAINT [PK_t_Work_Hours] PRIMARY KEY CLUSTERED
(
[WorkHoursID_PK]
) ON [PRIMARY] ,
CONSTRAINT [FK_t_Work_Hours_t_Work_People] FOREIGN KEY
(
[PeopleID_FK]
) REFERENCES [dbo].[t_Work_People] (
[PeopleID_PK]
)
) ON [PRIMARY]
GO
-----------------

What i have tried has'nt even been close to a solution.
UPDATE dbo.t_Work_Hours
SET WeekEnding = DATETIME ( ? , StartTime) . I have no idea where to go!

--------------------
WorkHoursID_PK, PeopleID_FK, JobID_FK, StartTime, EndTime, Title, WeekEnding
7, 40, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, J, (NULL)
8, 43, 3, 10/31/2005 6:00:00 AM, 10/31/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
9, 43, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
------------------If you insist on performing an UPDATE on the field, then some variation of the statement below should suffice:


...SET WeekEnding =dateadd(day, 8-datepart(dw, StartTime), StartTime)...


I would make WeekEnd a computed column like this:

alter table add Weekend as dateadd(day, 8-datepart(dw, StartTime), StartTime)|||Should be in here somewhere

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx|||thanks a bunch

automagical calculation of a field value

Hi All,
I have a table "AggregatedSales" which has a column called
"AggregatedSales.YearSum".
This column should contain a sum which can be derived directly from another
table called "Sales" by summing up all values of "Sales.Price" WHERE
"Sales.Year" = 2005.
Is it possible to have MS SQL Server automatically calculate the value for
"AggregatedSales.YearSum" when a new data record is inserted? I would like
to add a formula to the column "AggregatedSales.YearSum" that calculates the
value for this field depending on a SELECT-Statement with a SUM() - is that
or such like possible?
Any hints are highly appreciated!
Cheers, JanHi
You will need to do this though a trigger on the table where the insert gets
done.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:egvP8h45FHA.2984@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the
> value for this field depending on a SELECT-Statement with a SUM() - is
> that
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>|||Hi Jan
You may possibly want to consider using a view for AggregatedSales!
John
"Jan R?sner" wrote:

> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from anothe
r
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates t
he
> value for this field depending on a SELECT-Statement with a SUM() - is tha
t
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
>|||"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:egvP8h45FHA.2984@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the
> value for this field depending on a SELECT-Statement with a SUM() - is
> that
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
Create a view rather than a summary table. You can consider making it an
indexed view. You should avoid storing calculated results whenever possible.
David Portas
SQL Server MVP
--

Autoincrementing file share

Hi all,
I'm really new at reporting services. I'm hoping someone can help.
I have a subscription that runs daily. The MSDN tells me
"AutoIncrement, the report server appends a timestamp or number to the file
name to distinguish it from existing files of the same name".
Currently the number is being appended to the file name daily, however I
would like the timestamp appended. Any ideas how to achieve this?
Thanks
DebbieSorry about the duplicate posts. I couldn't find the original one I posted.
Now that I have found it, I can't delete this one. >_<
Debbie
"Debbie" wrote:
> Hi all,
> I'm really new at reporting services. I'm hoping someone can help.
> I have a subscription that runs daily. The MSDN tells me
> "AutoIncrement, the report server appends a timestamp or number to the file
> name to distinguish it from existing files of the same name".
> Currently the number is being appended to the file name daily, however I
> would like the timestamp appended. Any ideas how to achieve this?
> Thanks
> Debbie
>

Auto-incremented fields error

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

Code Snippet

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

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

Any ideas on what is going on here?

Thanks.

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

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

Hope that helps,

John

autoincremental field

How can we give serial numbers to the records in a tableUse RowNumber() function. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3quq.asp?frame=true
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"vinesh" <vineshk@.visitomega.com> wrote in message
news:%23sgjfUJcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> How can we give serial numbers to the records in a table
>

Autoincrement record position in a view

Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?

For example let's say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?

recno persons
1person1
2person2
3person3

Please help me

I will be very grateful if you also reply your answers also and to my email

Email: stavrinc@.hotmail.com

Thank you

Christos StavrinouWith FoxPro you can use the RECNO() function:

SELECT RECNO() AS recno, persons FROM myTable

This is not possible with SQL Server, and I am relatively sure it is not possible with Access.

Terri|||I should have said that this is possible with SQL Server only by inserting your result set into a #TEMP table which has an Identity column.view post 368541

Terri

autoincrement question sql 2005 server

How add column autoincrement in sql server 2005 use SQL Server Management
Studio?

Tominfo (informatyk@.fitness[CUT]authority.pl) writes:

Quote:

Originally Posted by

How add column autoincrement in sql server 2005 use SQL Server Management
Studio?

For an existing column? That's a bit of work, because you need to copy
the table to a new definition. There is now ALTER TABLE command to add/drop
identiity. It may look simple in SSMS, but Mgmt Studio performs all that
drop affair behind your back. That would be OK if it knew how to do it,
but it doesn't, so if things go wrong you may mess up your database.

Of course, if you run SQL commands, you may mess the database on your
own as well, but at least you knew what you did.

--
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

Auto-Increment Primary key Sqlce Problem

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

|||

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

Auto-increment PK - Use MS or Grow your own ?

SQL 2000

I thought I would throw this out there for some feedback from others.

I'd like to know if you feel using MS auto-increment field is a good
solution these days or should one grow their own ?

Thanks,

Me.Definitely use SQL Servers to auto-increment the primary key field.
Then there is no chance of duplicates.|||I can give you a definite Maybe.

I've used both depending on:

* Do you care that numbers may be missing in the sequence if insert
transactions get rolled back?
* Are you making the Primary Key the table's clustered index?
* Do you need to populate the value of that key into other tables within
the same procedure?

<csomberg@.dwr.com> wrote in message
news:1109115558.223984.69950@.z14g2000cwz.googlegro ups.com...
> SQL 2000
> I thought I would throw this out there for some feedback from others.
> I'd like to know if you feel using MS auto-increment field is a good
> solution these days or should one grow their own ?
> Thanks,
> Me.|||This question is bound to start a few of the regulars off on one of
their favorite arguements. Still to add my tuppence worth, I'd say it's
OK to use it under some circumstances. When and where depends entirely
on the solution you are providing and the data you are working with.

Surrogate keys in my opinion are valid and often much more simple than
a primary key of several columns. Having said that, you do get
instances with 'gaps' in the number sequence so this may not be
something you want. You could go down the route of generating a
surrogate key yourself (your original question), but I would hesitate
if IDENTITY offers you the same advantages as you may introduce more
problems depending on your approach. If possible and where logical, use
a properly defined primary key, but use your head and decide what is
most appropriate.

I know full well that some of the regulars will not agree with me and
that some will. However, it is my opinion and they are entitled to
theirs. I would suggest looking up 'Occams Razor' for anyone who
disagrees ( http://pespmc1.vub.ac.be/ASC/OCCAM'_RAZOR.html )

Going back to your question, if you 'grow your own' then this seems
reasonable if you are doing something that using IDENTITY doesn't allow
you. If it's the same, then why bother ?

Ryan

csomberg@.dwr.com wrote:
> SQL 2000
> I thought I would throw this out there for some feedback from others.
> I'd like to know if you feel using MS auto-increment field is a good
> solution these days or should one grow their own ?
> Thanks,
> Me.|||If you want an artificial key then use the feature provided - it's the
most efficient method. The harder question is, do you really want an
artificial key?

--
David Portas
SQL Server MVP
--|||(csomberg@.dwr.com) writes:
> I thought I would throw this out there for some feedback from others.
> I'd like to know if you feel using MS auto-increment field is a good
> solution these days or should one grow their own ?

Depends. If you need consecutive numbers, forget about IDENTITY. If you
want high scalability and don't want to have a hot spot on the current
key value, use IDENTITY.

Another situation where IDENTITY is difficult is when you insert many rows
in one table, and then need to know the values for inserts into a child
table.

If none of this applies, it's a toss-up. IDENTITY is somewhat simpler to
use, but there are some gotchas in odd situations. Rolling your own
is simple as well.

A general remark is that whatever method you use, don't use it for
every table. For "top" concepts like customers, orders, products it
may be inevitable. But for derived concepts that refers to other
concepts, there is rarely any need for artificial keys.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 23 Feb 2005 01:16:27 -0800, "Ryan" <ryanofford@.hotmail.com> wrote:

>This question is bound to start a few of the regulars off on one of
>their favorite arguements. Still to add my tuppence worth, I'd say it's

Don't you mean "tupple's worth"? <g>|||On 22 Feb 2005 15:39:18 -0800, csomberg@.dwr.com wrote:

>SQL 2000
>I thought I would throw this out there for some feedback from others.
>I'd like to know if you feel using MS auto-increment field is a good
>solution these days or should one grow their own ?
>Thanks,
>Me.

I often use a mix.

As has been pointed out here, if you need to ensure there no gaps, IDENTITY is
not your friend. To me, however, the kind of numbering that needs no gaps
should be a logical key, not a physical/surrogate key, so that's no issue.

Where I have found IDENTITY to be limiting are as follows.

1. I want to be able to merge data sets from 2 or more separate databases
without using a proprietary replication system. In this case, I like to use
some kind of variation on the GUID. Using the global key as the primary key
keeps merge processes simpler than if the global key is used in addition to a
local primary key

2. I need to generate sequences of master-detail sets in stored procedures.

In case #2, the problem is that we want to try to use set operations, not
cursors, and there's no good way to figure out the IDs of the master records
created in one query, so you can use them to create matching details in a
subsequent query. On the other hand, if you have a shared counter, you obtain
a count of the master records to be added, get the current counter value, and
update the counter, adding the master-count to its value. Reading and
updating the counter can be in its own short transaction to reduce blocking
overhead, since we should not care if we add a gap, but don't end up adding
the records with those keys.|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:1f4q11d4hnd9sa440mst33f4chnis0ffma@.4ax.com...
> On 23 Feb 2005 01:16:27 -0800, "Ryan" <ryanofford@.hotmail.com> wrote:
>>This question is bound to start a few of the regulars off on one of
>>their favorite arguements. Still to add my tuppence worth, I'd say it's
> Don't you mean "tupple's worth"? <g
From the definition of the DIF file format:
It uses the terms vector and TUPLE.
You may generally interpret vector as column and tuple as row.

From the Mary Poppins Sound Track:
With TUPPENCE for paper and strings
You can have your own set of wings
With your feet on the ground
You're a bird in a flight
With your fist holding tight
To the string of your kite

I hope I've cleared that up,

Oh my ... It seems I'm out of Pinot.|||On Thu, 24 Feb 2005 05:02:55 GMT, "David Rawheiser" <rawhide58@.hotmail.com>
wrote:

>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:1f4q11d4hnd9sa440mst33f4chnis0ffma@.4ax.com...
>> On 23 Feb 2005 01:16:27 -0800, "Ryan" <ryanofford@.hotmail.com> wrote:
>>
>>>This question is bound to start a few of the regulars off on one of
>>>their favorite arguements. Still to add my tuppence worth, I'd say it's
>>
>> Don't you mean "tupple's worth"? <g>
>From the definition of the DIF file format:
> It uses the terms vector and TUPLE.
> You may generally interpret vector as column and tuple as row.
>From the Mary Poppins Sound Track:
> With TUPPENCE for paper and strings
> You can have your own set of wings
> With your feet on the ground
> You're a bird in a flight
> With your fist holding tight
> To the string of your kite
>I hope I've cleared that up,
>Oh my ... It seems I'm out of Pinot.

LOL|||Oh no ! I'm going to be humming that all day now ! :-)

> From the Mary Poppins Sound Track:
> With TUPPENCE for paper and strings
> You can have your own set of wings
> With your feet on the ground
> You're a bird in a flight
> With your fist holding tight
> To the string of your kite|||>> I'd like to know if you feel using MS auto-increment field is a good
solution these days or should one grow their own ? <<

What did you want to use it for?

It is fine for adding a reference number to a cursor, which is a
sequential file structure. But you would never use it inside the
schema for anything. That would that your data model is all screwed up
and has the PHYSICAL state of the machine mixed with the LOGICAL data
model.

Newbies often use IDENTITY and the like to substitute for the pointer
chains they had in IDMS, IMS, TOTAL and other pre-RDBMS databases.

if they are really screwed up, they use them for keys and do not have
natural keys. There is no way to verifiy or validate the data and the
schema loses data integrity.

If I told you that the best key is the 17 digit Hebrew number which God
assigned to all things in creation, you would think I was nuts. There
is no magic universal key; you actually have to do some work when you
design a schema.

We have a lot of problems with terminology on this one, so let me get
that out of the way.

There is no such thing as a "universal, one-size-fits-all" key. Just
as no two sets of entities are the same, the attributes that make them
unique have to be found in the reality of the data. Here is my
classification of types of keys:

natural artificial exposed surrogate
================================================== ================
Constructed from reality |
of the data model | Y N N Y
|
verifiable in reality | Y N N N
|
verifiable in itself | Y Y N N
|
visible to the user | Y Y Y N

1) A natural key is a subset of attributes which occur in a table and
act as a unique identifier. They are seen by the user. You can go to
the external reality and verify them. you would also like to have some
validation rule. Example: UPC codes on consumer goods (read the
package barcode) and validate them with a check digit or a
manufacturer's website, geographical co-ordinates (get a GPS).

2) An artificial key is an extra attribute added to the table which is
seen by the user. It does not exist in the external reality, but can
be verified for syntax or check digits inside itself.

Example: the open codes in the UPC scheme which a user can assign to
his own stuff. The check digits still work, but you have to verify
them inside your own enterprise.

If you have to construct a key yourself, it takes time to deisgn them,
to invetn a validation rule, etc.

3) An "exposed physical locator" is not based on attributes in the data
model and is exposed to user. There is no way to predict it or verify
it. The system obtains a value thru some physical process in the
storage hardware totally unrelated to the logical data model. Example:
IDENTITY columns, other proprietary, non-relaitonal auto-numbering
devices.

Technically, these are not really keys at all, sinc they are attributes
of the PHYSICAL storage and are not even part of the LOGICAL data
model. But they are handy for lazry, non-RDBMS programmers who don't
want to research or think! This is the worst way to program in SQL.

4) A surrogate key is system generated to replace the actual key behind
the covers where the user never sees it. It is based on attributes in
the table. Example: Teradata hashing algorithms, pointer chains.

The fact that you can never see it or use it for DELETE and UPDATE or
create it for INSERT is vital. When users can get to them, they will
screw up the data integrity by getting the real keys and these physical
locators out of synch. The system must maintain them.

** Notice that people get "exposed physical locator" and surrogate
mixed up; they are totally different concepts. **

An appeal to authority, with a quote from Dr. Codd: "..Database users
may cause the system to generate or delete a surrogate, but they have
no control over its value, nor is its value ever displayed to them
..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending
the database relational model to capture more meaning. ACM
Transactions on Database Systems, 4(4). pp. 397-434.

This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means never
used in queries, DRI or anything else that a user does.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result
that some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased to have one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] -
is to introduce entity domains which contain system-assigned
surrogates. Database users may cause the system to generate or delete
a surrogate, but they have no control over its value, nor is its value
ever displayed to them...." (Codd in ACM TODS, pp 409-410).

References

Codd, E. (1979), Extending the database relational model to capture
more meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

The steps for finding a key are

1) Look for an industry standard and the trusted source that maintains
it.

2) Look for a natural key in the attributes. Example: (longitude,
latitude) makes a good key for a geographical location.

3) If you must design a new identifier, plan it carefully -- especially
if people will see and use it. You have to be able to validate it in
application programs, so you need a regular expression, other syntax
rule and/or check digits. You have to be able to be verify in the
reality of the model or with a trusted source.

Auto-Increment of varchar primary key

Hi All
I am looking for a bit of advice.
I am in the process of creating a database in which it has been decided that
all primary keys are going to varchar(40). Not my decision, but anyway.
When inserting into each table it will be possible to specify a value for
the primary, but if not specified a value should be auto-generated. That
means that the values in the primary key field can be a mixture of both
numbers and letters, but if auto-generated it should just be a number.
What be the best way to make this autogenerated values if no value is being
specified in the insert?
TIA
KlausDepends really, if auto-generated does it just need to be a number? Any old
number, or a specific format and range?
I always recommend putting a surrogate key on the tables and use that as the
foriegn key and inside the application (not for display purposes, but for
use as the value in a listbox for instance), that can be a int column with
the IDENTITY property, not null and have a unique constraint on it.
You could set the value of the primary key to that if not specified, that
would save calculating a new unique number.
Otherwise, you could use an 'instead of' trigger, for example...
Instead of using MAX, you could take the value from a table that holds the
last number used.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Klaus" <Klaus@.discussions.microsoft.com> wrote in message
news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
> Hi All
> I am looking for a bit of advice.
> I am in the process of creating a database in which it has been decided
> that
> all primary keys are going to varchar(40). Not my decision, but anyway.
> When inserting into each table it will be possible to specify a value for
> the primary, but if not specified a value should be auto-generated. That
> means that the values in the primary key field can be a mixture of both
> numbers and letters, but if auto-generated it should just be a number.
> What be the best way to make this autogenerated values if no value is
> being
> specified in the insert?
> TIA
> Klaus
>|||Thanks a lot, Tony. That was very helpfull.
I will create a unique field on each of my tables. The value for this will
be auto-generated using identity. A trigger will then keep an eye on the
inserts. If no value is being specified for the Primary key, the Identity
value will be copied into the varchar(40) primary key field.
-- Klaus
"Tony Rogerson" wrote:

> Depends really, if auto-generated does it just need to be a number? Any ol
d
> number, or a specific format and range?
> I always recommend putting a surrogate key on the tables and use that as t
he
> foriegn key and inside the application (not for display purposes, but for
> use as the value in a listbox for instance), that can be a int column with
> the IDENTITY property, not null and have a unique constraint on it.
> You could set the value of the primary key to that if not specified, that
> would save calculating a new unique number.
> Otherwise, you could use an 'instead of' trigger, for example...
> Instead of using MAX, you could take the value from a table that holds the
> last number used.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Klaus" <Klaus@.discussions.microsoft.com> wrote in message
> news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
>
>|||The second part of Tony's point should not be lost. You should use that int
key as the FK for relationships with other tables. If you need to show your
client the benefit of using an int instead of a varchar(40). Load up a
couple of tables with some test data. Perform join's using varchar(40) as
the keys and then the same using int as the key. The performance difference
is noticable.
So use an int (or even bigint) PK, put a unique constraint on the
varchar(40) column and for all business logic purposes, the varchar(40) fiel
d
is the "key". But behind the scenes in the database the far more efficient
int is the key.
John Scragg
"Klaus" wrote:
> Thanks a lot, Tony. That was very helpfull.
> I will create a unique field on each of my tables. The value for this will
> be auto-generated using identity. A trigger will then keep an eye on the
> inserts. If no value is being specified for the Primary key, the Identity
> value will be copied into the varchar(40) primary key field.
> -- Klaus
> "Tony Rogerson" wrote:
>|||Also, if you need the data in a varchar(40) field you can use a calculated
column (if they dont need to enter it).
I concurr with Tony & John, if you're doing joins, definately use the INT
field as the joining field, joining on varchar fields gets very slow at
medium to high data volumes.
create table ( id int identity(1,1) primary key , myPK AS cast( ID as
varchar(40)) )
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:EDB86798-0F96-415A-9D8D-733ED2E0CA02@.microsoft.com...
> The second part of Tony's point should not be lost. You should use that
int
> key as the FK for relationships with other tables. If you need to show
your
> client the benefit of using an int instead of a varchar(40). Load up a
> couple of tables with some test data. Perform join's using varchar(40) as
> the keys and then the same using int as the key. The performance
difference
> is noticable.
> So use an int (or even bigint) PK, put a unique constraint on the
> varchar(40) column and for all business logic purposes, the varchar(40)
field
> is the "key". But behind the scenes in the database the far more
efficient
> int is the key.
> John Scragg
> "Klaus" wrote:
>
will
Identity
Any old
as the
for
with
that
the
violation,
decided
anyway.
value for
That
both
number.
is

auto-increment nvarchar column

Hi experts!

The situation:I have a table containing the MemberID and related member's info. The MemberID is the pk and should be incremented for every new member. However, the ID is in "nvarchar" type, since it consists of an "IM" prefix. I used the following stored procedure to insert new member to the table.

CREATE PROCEDURE dbo.spInsertNewMember
@.parMemberTitle nvarchar(2),
@.parMemberFirstName nvarchar(40),
@.parMemberLastName nvarchar(40)
AS
DECLARE @.LastMemberID AS nvarchar(16)
DECLARE @.NextMemberID AS nvarchar(16)
SET @.LastMemberID = (SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName) VALUES (@.NextMemberID, @.parMemberTitle, @.parMemberFirstName, @.parMemberLastName)
RETURN

I want to ask if this sp can handle any concurrent insert to the MemberID table.

Thanks.

Hi,

why arent you doing the evaluation in the INSERT statement rather than separate ? This would minimize the concurrency effect.

(SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName)
SELECT 'IM'+ CAST(RIGHT(ISNULL(MAX(MemberID),'IM000000'),6) + 1 AS CHAR(6)),
@.parMemberTitle,
@.parMemberFirstName,
@.parMemberLastName
FROM MemberInfo

If you really want to be sure about this you have to specify a TABLOCK.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for your suggestion...

Since I have been using mysql for quite a while and thus not familiar with mssql.

what I am really looking for is something similar to "SELECT .... FOR UPDATE" in mysql.

I will try your suggest that later.

Thanks

Auto-increment my primary key: why 2 instead of 1?

Hi all,

I have a table where I have my ProdPK set up as Primary key, turned on "Is Identity" and set the Identity increment to 1. But each time I add a new item, the number incremented by 2... I have couple of other tables and they are all fine, just this particular table increased twice as it should. I check the setting against other tables and everything seems to be the same.

By the way, this is adding the data to the table inside MS SQL Server Management Studio manually. I haven't done anything in the ASP.NET page yet.

Thank you very much,

Kenny.

That's weird. I would double check the identity column settings, and maybe check for triggers on the table?

|||

Thanks. I did checked the table's setting and the auto-increment was set to "1". Other settings are identical to my other tables. Any suggestions?

Thanks again,

Kenny.

|||

Can you post the table script and the results you observe by executing "dbcc checkident ( YourTableName )" ? Have you made sure that there are no triggers defined on the table ?

|||

Here is the table's script:

1USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]2GO3/****** Object: Table [dbo].[T_PRODUCTS] Script Date: 12/22/2007 11:58:52 ******/4SET ANSI_NULLS ON5GO6SET QUOTED_IDENTIFIER ON7GO8SET ANSI_PADDING ON9GO10CREATE TABLE [dbo].[T_PRODUCTS](11[PROD_ID] [int] IDENTITY(1,1) NOT NULL,12[ACC_TYPE_ID_FK] [int] NOT NULL,13[PROD_NAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,14[PROD_DESCR] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,15[PROD_MODEL] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,16[PROD_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,17[PROD_PRICE] [smallmoney] NULL,18[PROD_DATE_ADDED] [datetime] NOT NULL CONSTRAINT [DF_T_PRODUCTS_PROD_DATE_ADDED] DEFAULT (getdate()),19[PROD_ADDED_BY] [uniqueidentifier] NULL,20[PROD_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,21[PROD_NOTES] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,22[PROD_PIC_S] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,23[PROD_PIC_L] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,24[PROD_NEW] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,25 CONSTRAINT [PK_T_PRODUCTS] PRIMARY KEY CLUSTERED26(27[PROD_ID] ASC28)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]29) ON [PRIMARY]3031GO32SET ANSI_PADDING OFF33GO34USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]35GO36ALTER TABLE [dbo].[T_PRODUCTS] WITH NOCHECK ADD CONSTRAINT [ACT_TYPE_ID_FK] FOREIGN KEY([ACC_TYPE_ID_FK])37REFERENCES [dbo].[T_ACC_TYPES] ([ACC_TYPE_ID])38NOT FOR REPLICATION

This is what it return when I run the dbcc:

Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Currently there are only two rows in my database, with the primary key values 2 and 4. It skipped 1 and 3.

Thank you,

Kenny.

|||

I don't know what exactly is the problem, but my database primary key started at 2, then 4, then 8... After 8, everything then become normal, meaning they increased correctly by 1!!!

I still don't know why, but it solved the problem itself I guess!

Thanks all,

Kenny.