Backing Up System Databases...

I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
JasonBoth master and msdb should be done daily. You could do model every once in
a while. Generally, it doesn't change - unless you choose to add an object
that you want to appear in all future DB's.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
Jason|||Or just include model for daily backup anyway since it's so tiny.
Linchi
"Tom Moreau" wrote:

> Both master and msdb should be done daily. You could do model every once
in
> a while. Generally, it doesn't change - unless you choose to add an objec
t
> that you want to appear in all future DB's.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
> message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
> I am rather new to the area of performing DBA type activities (so don't be
> too hard on me).
> When I create a maintenance plan, which of the system databases should I
> choose to back up (if it is not necessary or recommended to back all of th
em
> up)?
> Thank you,
> Jason
>

Backing Up System Databases...

I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
JasonBoth master and msdb should be done daily. You could do model every once in
a while. Generally, it doesn't change - unless you choose to add an object
that you want to appear in all future DB's.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
Jason|||Or just include model for daily backup anyway since it's so tiny.
Linchi
"Tom Moreau" wrote:
> Both master and msdb should be done daily. You could do model every once in
> a while. Generally, it doesn't change - unless you choose to add an object
> that you want to appear in all future DB's.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
> message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
> I am rather new to the area of performing DBA type activities (so don't be
> too hard on me).
> When I create a maintenance plan, which of the system databases should I
> choose to back up (if it is not necessary or recommended to back all of them
> up)?
> Thank you,
> Jason
>sql

Backing up SQL DB

We're trying to create a .bak file to send it to our site's admin to restore it on the

server:

1. When I try to do that get this error:
http://www.radiorient.net/errors/export_sql.gif

Why is that? and how can I fix it?

2. Should we add both files of the db (.MDF, and .LDF) or only the .MDF file?

Can please someone help me with this, Thanx.

Do not bakcup a datbase to its own data/log file, instead, you can backup to any other file that can be written by SQL Server. The purpose of backup is to gurantee the database can be recovered in case of database corruption (usually the physical files are corrupted), so bakcup a database to its data/log files doesn't make sense, right?

For more information about backup, please refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp

|||

Thanx, we did it, we have some debugging to do now, but at least the DB is online, I hate H-Sphere, I worked with many control panels that is a lot easier.

Now when we upload image using the admin area, it doesn't upload the image on the server, we checked the permission of the image folder and it's 777, so what could be wrong?!

Backing up my DB on host

Hi,
I can make backup from my database but the SQL Server is on a
host(internet). The backup sets are placed on the host.
How can I create backups the way that backup sets relocate to my local PC.
Any help would be greatly appreciated.
Amin
I doubt that you will be able to do this. In order for it to happen your
SQL Server machine would have to be able to see the hard drives on your
machine. This talks about the database maintenance wizard, but the rules
apply to normal T-SQL backups as well:
http://support.microsoft.com/default...b;en-us;555128
What is your objective? Do you want to get your production data which is
located on the internet onto your machine? How far away from your host are
you? One option (although not a fun one) is driving to your host and
grabbing a tape/CD/DVD containing your backup file. Another option would be
to create a linked server from your PC (or local server) to your remote SQL
Server. You could then perform an INSERT INTO...SELECT (from the linked
server). One final option: Use a tool or script that creates insert
statements for you. you could then issue these statements on your local
machine.
Keith
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23bu4uUxmEHA.2616@.tk2msftngp13.phx.gbl...
> Hi,
> I can make backup from my database but the SQL Server is on a
> host(internet). The backup sets are placed on the host.
> How can I create backups the way that backup sets relocate to my local PC.
> Any help would be greatly appreciated.
> Amin
>
|||Actually, you should be able to do it if they let you run backups. You need
to temporarily open up your computer to the internet and allow guest write
access to a file share on the computer. You can then get the IP address of
your computer and use the following command:
BACKUP DATABASE databasename
TO DISK = '\\10.1.0.1\share$\directory\file.bak'
Does your IP actually let you run backups though? That's kind of scary.
"Keith Kratochvil" wrote:

> I doubt that you will be able to do this. In order for it to happen your
> SQL Server machine would have to be able to see the hard drives on your
> machine. This talks about the database maintenance wizard, but the rules
> apply to normal T-SQL backups as well:
> http://support.microsoft.com/default...b;en-us;555128
> What is your objective? Do you want to get your production data which is
> located on the internet onto your machine? How far away from your host are
> you? One option (although not a fun one) is driving to your host and
> grabbing a tape/CD/DVD containing your backup file. Another option would be
> to create a linked server from your PC (or local server) to your remote SQL
> Server. You could then perform an INSERT INTO...SELECT (from the linked
> server). One final option: Use a tool or script that creates insert
> statements for you. you could then issue these statements on your local
> machine.
> --
> Keith
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23bu4uUxmEHA.2616@.tk2msftngp13.phx.gbl...
>
|||I can't imagine that the network admins would want to turn off the firewall
or expose a machine on the internet.
If the [client] machine is available (visible) on the internet, then the
T-SQL BACKUP command backing up to the UNC should work (as long as the
account running SQL Server on the remote machine has rights to the share on
your computer). This is described within the KB article.
Keith
"MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
news:45E7E013-C3E6-49BC-98BE-43BC153AD276@.microsoft.com...
> Actually, you should be able to do it if they let you run backups. You
need
> to temporarily open up your computer to the internet and allow guest write
> access to a file share on the computer. You can then get the IP address
of[vbcol=seagreen]
> your computer and use the following command:
> BACKUP DATABASE databasename
> TO DISK = '\\10.1.0.1\share$\directory\file.bak'
> Does your IP actually let you run backups though? That's kind of scary.
> "Keith Kratochvil" wrote:
your[vbcol=seagreen]
rules[vbcol=seagreen]
is[vbcol=seagreen]
are[vbcol=seagreen]
would be[vbcol=seagreen]
SQL[vbcol=seagreen]
linked[vbcol=seagreen]
PC.[vbcol=seagreen]
sql

Backing up my DB on host

Hi,
I can make backup from my database but the SQL Server is on a
host(internet). The backup sets are placed on the host.
How can I create backups the way that backup sets relocate to my local PC.
Any help would be greatly appreciated.
AminI doubt that you will be able to do this. In order for it to happen your
SQL Server machine would have to be able to see the hard drives on your
machine. This talks about the database maintenance wizard, but the rules
apply to normal T-SQL backups as well:
http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
What is your objective? Do you want to get your production data which is
located on the internet onto your machine? How far away from your host are
you? One option (although not a fun one) is driving to your host and
grabbing a tape/CD/DVD containing your backup file. Another option would be
to create a linked server from your PC (or local server) to your remote SQL
Server. You could then perform an INSERT INTO...SELECT (from the linked
server). One final option: Use a tool or script that creates insert
statements for you. you could then issue these statements on your local
machine.
--
Keith
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23bu4uUxmEHA.2616@.tk2msftngp13.phx.gbl...
> Hi,
> I can make backup from my database but the SQL Server is on a
> host(internet). The backup sets are placed on the host.
> How can I create backups the way that backup sets relocate to my local PC.
> Any help would be greatly appreciated.
> Amin
>|||I can't imagine that the network admins would want to turn off the firewall
or expose a machine on the internet.
If the [client] machine is available (visible) on the internet, then the
T-SQL BACKUP command backing up to the UNC should work (as long as the
account running SQL Server on the remote machine has rights to the share on
your computer). This is described within the KB article.
--
Keith
"MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
news:45E7E013-C3E6-49BC-98BE-43BC153AD276@.microsoft.com...
> Actually, you should be able to do it if they let you run backups. You
need
> to temporarily open up your computer to the internet and allow guest write
> access to a file share on the computer. You can then get the IP address
of
> your computer and use the following command:
> BACKUP DATABASE databasename
> TO DISK = '\\10.1.0.1\share$\directory\file.bak'
> Does your IP actually let you run backups though? That's kind of scary.
> "Keith Kratochvil" wrote:
> > I doubt that you will be able to do this. In order for it to happen
your
> > SQL Server machine would have to be able to see the hard drives on your
> > machine. This talks about the database maintenance wizard, but the
rules
> > apply to normal T-SQL backups as well:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >
> > What is your objective? Do you want to get your production data which
is
> > located on the internet onto your machine? How far away from your host
are
> > you? One option (although not a fun one) is driving to your host and
> > grabbing a tape/CD/DVD containing your backup file. Another option
would be
> > to create a linked server from your PC (or local server) to your remote
SQL
> > Server. You could then perform an INSERT INTO...SELECT (from the
linked
> > server). One final option: Use a tool or script that creates insert
> > statements for you. you could then issue these statements on your local
> > machine.
> >
> > --
> > Keith
> >
> >
> > "Amin Sobati" <amins@.morva.net> wrote in message
> > news:%23bu4uUxmEHA.2616@.tk2msftngp13.phx.gbl...
> > > Hi,
> > > I can make backup from my database but the SQL Server is on a
> > > host(internet). The backup sets are placed on the host.
> > > How can I create backups the way that backup sets relocate to my local
PC.
> > > Any help would be greatly appreciated.
> > > Amin
> > >
> > >
> >
> >

Backing Up Muliple Databases in Same Instance

I've 6 databases in the same instance which I wish to se a backup routine
for. How can I select all the databases at a go and create a backup procedure
just once in enerprise manager. I tried highlighting all he databases, but
each time I click backup up. he sysem comes up with one database at a time
for me o create backup for.
Regards
Is this something you want to schedule or do only once?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
> I've 6 databases in the same instance which I wish to se a backup routine
> for. How can I select all the databases at a go and create a backup procedure
> just once in enerprise manager. I tried highlighting all he databases, but
> each time I click backup up. he sysem comes up with one database at a time
> for me o create backup for.
> --
> Regards
|||I want to schedule it.
Regards
"Tibor Karaszi" wrote:

> Is this something you want to schedule or do only once?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
> news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
>
|||Create a Maintenance Plans. The regular backup dialog doesn't support selecting multiple databases,
but a Maint Plans does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:7EFD5154-BBB8-40D0-8791-51EF2B744CC3@.microsoft.com...[vbcol=seagreen]
>I want to schedule it.
> --
> Regards
>
> "Tibor Karaszi" wrote:

Backing Up Muliple Databases in Same Instance

I've 6 databases in the same instance which I wish to se a backup routine
for. How can I select all the databases at a go and create a backup procedure
just once in enerprise manager. I tried highlighting all he databases, but
each time I click backup up. he sysem comes up with one database at a time
for me o create backup for.
--
RegardsIs this something you want to schedule or do only once?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
> I've 6 databases in the same instance which I wish to se a backup routine
> for. How can I select all the databases at a go and create a backup procedure
> just once in enerprise manager. I tried highlighting all he databases, but
> each time I click backup up. he sysem comes up with one database at a time
> for me o create backup for.
> --
> Regards|||I want to schedule it.
--
Regards
"Tibor Karaszi" wrote:
> Is this something you want to schedule or do only once?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
> news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
> > I've 6 databases in the same instance which I wish to se a backup routine
> > for. How can I select all the databases at a go and create a backup procedure
> > just once in enerprise manager. I tried highlighting all he databases, but
> > each time I click backup up. he sysem comes up with one database at a time
> > for me o create backup for.
> > --
> > Regards
>|||Create a Maintenance Plans. The regular backup dialog doesn't support selecting multiple databases,
but a Maint Plans does.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:7EFD5154-BBB8-40D0-8791-51EF2B744CC3@.microsoft.com...
>I want to schedule it.
> --
> Regards
>
> "Tibor Karaszi" wrote:
>> Is this something you want to schedule or do only once?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
>> news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
>> > I've 6 databases in the same instance which I wish to se a backup routine
>> > for. How can I select all the databases at a go and create a backup procedure
>> > just once in enerprise manager. I tried highlighting all he databases, but
>> > each time I click backup up. he sysem comes up with one database at a time
>> > for me o create backup for.
>> > --
>> > Regards
>>

Backing Up Muliple Databases in Same Instance

I've 6 databases in the same instance which I wish to se a backup routine
for. How can I select all the databases at a go and create a backup procedur
e
just once in enerprise manager. I tried highlighting all he databases, but
each time I click backup up. he sysem comes up with one database at a time
for me o create backup for.
--
RegardsIs this something you want to schedule or do only once?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
> I've 6 databases in the same instance which I wish to se a backup routine
> for. How can I select all the databases at a go and create a backup proced
ure
> just once in enerprise manager. I tried highlighting all he databases, but
> each time I click backup up. he sysem comes up with one database at a time
> for me o create backup for.
> --
> Regards|||I want to schedule it.
--
Regards
"Tibor Karaszi" wrote:

> Is this something you want to schedule or do only once?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
> news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
>|||Create a Maintenance Plans. The regular backup dialog doesn't support select
ing multiple databases,
but a Maint Plans does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:7EFD5154-BBB8-40D0-8791-51EF2B744CC3@.microsoft.com...[vbcol=seagreen]
>I want to schedule it.
> --
> Regards
>
> "Tibor Karaszi" wrote:
>

Backing up an SQL Express Database

Hi,
Can someone tell how I create a backup of an SQL Express Database, so that I
can restore it into SQL Server 2005.
Many thanks
Wayne
You should be able to use T-SQL:
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
Keith
"Wayne Gibson" <wayne.gibson@.rendersoft.com> wrote in message
news:d9u24i$68b$1$8302bc10@.news.demon.co.uk...
> Hi,
> Can someone tell how I create a backup of an SQL Express Database, so that
> I can restore it into SQL Server 2005.
> Many thanks
> Wayne
>

Backing up an SQL Express Database

Hi,
Can someone tell how I create a backup of an SQL Express Database, so that I
can restore it into SQL Server 2005.
Many thanks
Wayne
You should be able to use T-SQL:
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
Keith
"Wayne Gibson" <wayne.gibson@.rendersoft.com> wrote in message
news:d9u24i$68b$1$8302bc10@.news.demon.co.uk...
> Hi,
> Can someone tell how I create a backup of an SQL Express Database, so that
> I can restore it into SQL Server 2005.
> Many thanks
> Wayne
>

Backing up an SQL Express Database

Hi,
Can someone tell how I create a backup of an SQL Express Database, so that I
can restore it into SQL Server 2005.
Many thanks
WayneYou should be able to use T-SQL:
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
--
Keith
"Wayne Gibson" <wayne.gibson@.rendersoft.com> wrote in message
news:d9u24i$68b$1$8302bc10@.news.demon.co.uk...
> Hi,
> Can someone tell how I create a backup of an SQL Express Database, so that
> I can restore it into SQL Server 2005.
> Many thanks
> Wayne
>sql

Backing up an SQL Express Database

Hi,
Can someone tell how I create a backup of an SQL Express Database, so that I
can restore it into SQL Server 2005.
Many thanks
WayneYou should be able to use T-SQL:
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
Keith
"Wayne Gibson" <wayne.gibson@.rendersoft.com> wrote in message
news:d9u24i$68b$1$8302bc10@.news.demon.co.uk...
> Hi,
> Can someone tell how I create a backup of an SQL Express Database, so that
> I can restore it into SQL Server 2005.
> Many thanks
> Wayne
>

Backing Up

As it can take a long time to create the full text index once the database
is restored I think it is a good idea to back up the full text catalog. What
is the best method for doing do ?
GMG,
Assuming you're using either SQL Server 7.0 or SQL Server 2000, you should
review KB article: 240867 (Q240867) "INF: How to Move, Copy, and Backup
Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
If you're using SQL Server 2005 (codename Yukon), the FT Catalog can be
backed up and restored with the database!
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"GMG" <nospam@.nospam.com> wrote in message
news:uDysw71tFHA.908@.tk2msftngp13.phx.gbl...
> As it can take a long time to create the full text index once the database
> is restored I think it is a good idea to back up the full text catalog.
> What
> is the best method for doing do ?
>
sql

Backing online database table data to local machine

I have an online SQL Server database provided by an ISP. I do not have
permission to create a backup device and I understand this is normal
practice.

I am not using Enterprise Manager to administer the online database.

I know I can back up the structure of the database using SQL
scripts.

My question is:

How do I back up on my own machine the data contained in the online
database tables I have created? If I were using Enterprise Manager I
could do it by downloading tables using the DTS facility but how can I
do it without Enterprise Manager?

Is there some work around which I have missed eg creating a csv file
of the data?

Best wishes for 2005 to all those helpful people in this newsgroup!
John MorganJohn Morgan (jfm@.XXwoodlander.co.uk) writes:
> I have an online SQL Server database provided by an ISP. I do not have
> permission to create a backup device and I understand this is normal
> practice.
> I am not using Enterprise Manager to administer the online database.
> I know I can back up the structure of the database using SQL
> scripts.
> My question is:
> How do I back up on my own machine the data contained in the online
> database tables I have created? If I were using Enterprise Manager I
> could do it by downloading tables using the DTS facility but how can I
> do it without Enterprise Manager?

You can take a proper backup from to your own machine, as the SQL
Server machine is not likely to have access to your disks, and anyway
I don't think network backups are not supported. But you seem to understand
that anyway, since you mention DTS.

DTS can be run with dtsrun from the command-line, but I assume that
you need Enterprise Manager to create the packages. Then again, I don't
know DTS, so don't take it from me.

But there is BCP. Run this query:

SELECT 'BCP ' + db_name() + '..' + name + ' out ' + name + '.bcp ' +
'-S Server -n -U usr -P pw'
FROM sysobjects
WHERE type = 'U'
AND objectproperty(id, 'IsMSShipped') = 0

The cut and paste that result into a BAT file that you run.

Here I have specified -n which means native format. You can use -c instead
to get character format, with tab delimiting columns and newline delimiting
lines. You can also specify other delimiters.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

avoid warning

We use a script in our build process to create a database. A single
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.
See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:

> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegro ups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>

avoid warning

We use a script in our build process to create a database. A single
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
> > We use a script in our build process to create a database. A single
> > script creates entire database, including stored procedures. We
> > occasionally
> > get a warning message "cannot create entry in sysdepends as .....
> > the procedure will still be created". I believe this is a harmless
> > warning
> > and happens because of the order of creation of stord procedure. Is
> > there a way to disable this warning.
> >
> >|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegroups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>

avoid warning

We use a script in our build process to create a database. A single
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:

> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
>|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegroups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>

Avoid getting same country twice

I have a DB containing trips around the world.
I want to create a menu with all the countries we visit but at the moment it returns the whole column. i.e. Brazil 5 times etc.
How do I avoid repititions?
I thought I could make an array and check it to see if the country was already there, but I think SQL must have a native routine to achieve the same thing.
Thanks
MFirst of all, you should check your data model and select statement you use to retrieve countries. Probably your SELECT is not defined properly if it returns multiple rows. Anyway, you can retrieve unique names using DISTINCT:

select DISTINCT CountryName
from ...|||cool, thanks er.. madafaka.
my SQL looks like this now, and works a treat:

"SELECT DISTINCT Country, url_link FROM dest_search ORDER BY country"

knowing me there's probably still a better way!
Incidentally the url_link is the path to the detail page and I gather doing it this way allows the search engines to follow the links. at first I put just:
"SELECT DISTINCT Country FROM dest_search ORDER BY country"
but that gave me an error because it excluded the url_link data.

Cheers
M

Avoid duplicate index while creating temp table

We are using SQL 2K with sp4.
In our stored procedure, we create a temp table (i.e. #tablename) and
various indexes within the temp table. If mutliple users execute this store
d
procedure at the same time, it would cause duplicate index error. As a
temporary fix, we attach an unique number to the index name. I like to know
what is the best way to solve this problem without extra work?
wingmanI posted a similar query yesterday ( in which I completely mispelled
temporary);
http://groups.google.co.uk/group/mi...b23b0ffd6?hl=en
the basic response was that the indexes can co-exist without naming
conflicts
Cheers
Will|||Wingman,
You can read about this in the BOL. They must be unique within a table or
view but do not need to be unique within a database. Each temporary table
(not global ones ##) is unique. SQL Server create a unique name per user.
AMB
"Wingman" wrote:

> We are using SQL 2K with sp4.
> In our stored procedure, we create a temp table (i.e. #tablename) and
> various indexes within the temp table. If mutliple users execute this sto
red
> procedure at the same time, it would cause duplicate index error. As a
> temporary fix, we attach an unique number to the index name. I like to kn
ow
> what is the best way to solve this problem without extra work?
> wingman|||dude, step back a bit, and think about a solution where you don't have
multipel users accessing the same temp table.
this has GOT to lead to integrity issues.

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1
1 2 3 4 5 6 7 8 9 10 11 12 13 Total 2004 80 60 70 95 70 85 60 90 110 55 90 90 50 1005 2005 5 5 10 20 5 5 5 5 5 10 75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1
1 2 3 4 5 6 7 8 9 10 11 12 13 Total AVG 2004 80 60 70 95 70 85 60 90 110 55 90 90 50 1005 77.31 2005 5 0 5 10 20 5 0 5 5 5 5 0 10 75 5.77 -93.8% -100.0% -92.9% -89.5% -71.4% -94.1% -100.0% -94.4% -95.5% -90.9% -94.4% -100.0% -80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It will be a great help to us if you can send the detail.Currently we are hanging on this issue.We not only need average,we need Count and total also .The Detail is involved expression to convert from minutes(from stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,
Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,
Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks|||

Even I am facing same problem. Can someone please explain step by step. Thanks in advance.