Backing up Only the Table data?

How can i just backup the table data of a database. without the sp, views Please its Veeeeeeeery Urgentetc...

You can use BCP to export the data on a table by table basis.

|||

I'm not sure what you are attempting to accomplish -you might find the Database Publishing Wizard useful.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||I want To Backup only the data in a database. without the Sp views etc.. the thing is , i wanna do this by using t-sq. can u pleeeeese help me. in the mean time i wanna the command for to stop a database running. can u pleeeese help me ASAP. im in the middle of aa development. thank you. from|||

Well,

If you are not going to read and try out our previous suggestions, then we probably aren't going to offer you much more.

You have two good suggestions -tell us what is wrong with them so we will know that you at least tried them.

|||sorry for nt telling you details, bt i cant do that method. because i wanna do that from the t_sql commands. imm building a advanced database maintenance module from vb6. so i cant use thirdparty app,lications. i wanna do it by the application it self. and please help me. i tried aal ur suggestions. even i cant do them , i serched how to do them in da net. so please help me.

Backing up one db table

Is it possible to only backup one table in a SQL database, or do you
have to back up everything?
TIA,
-BenBem
You can put the table on separate filegroup and then perfom backup of this
filegroup.
For more details please refer to the BOL.
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben|||No, backup is not possible for a single table. The lowest level possible is
a single data file. If you need a single table backup, you have to do it
with ther tools, not backup. You can use Data Transformation Services, a
T-SQL query, bcp.exe...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben|||Just be aware that RESTORE might not be possible, they way the OP wants...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OPrgM%23QiEHA.1276@.TK2MSFTNGP09.phx.gb
l...
> Bem
> You can put the table on separate filegroup and then perfom backup of this
> filegroup.
> For more details please refer to the BOL.
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
>sql

Backing up one db table

Is it possible to only backup one table in a SQL database, or do you
have to back up everything?
TIA,
-Ben
Bem
You can put the table on separate filegroup and then perfom backup of this
filegroup.
For more details please refer to the BOL.
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben
|||No, backup is not possible for a single table. The lowest level possible is
a single data file. If you need a single table backup, you have to do it
with ther tools, not backup. You can use Data Transformation Services, a
T-SQL query, bcp.exe...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben
|||Just be aware that RESTORE might not be possible, they way the OP wants...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OPrgM%23QiEHA.1276@.TK2MSFTNGP09.phx.gbl...
> Bem
> You can put the table on separate filegroup and then perfom backup of this
> filegroup.
> For more details please refer to the BOL.
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
>

Backing up one db table

Is it possible to only backup one table in a SQL database, or do you
have to back up everything?
TIA,
-BenBem
You can put the table on separate filegroup and then perfom backup of this
filegroup.
For more details please refer to the BOL.
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben|||No, backup is not possible for a single table. The lowest level possible is
a single data file. If you need a single table backup, you have to do it
with ther tools, not backup. You can use Data Transformation Services, a
T-SQL query, bcp.exe...
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben|||Just be aware that RESTORE might not be possible, they way the OP wants...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OPrgM%23QiEHA.1276@.TK2MSFTNGP09.phx.gbl...
> Bem
> You can put the table on separate filegroup and then perfom backup of this
> filegroup.
> For more details please refer to the BOL.
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > Is it possible to only backup one table in a SQL database, or do you
> > have to back up everything?
> >
> > TIA,
> >
> > -Ben
>

backing up a single table

Hello:
Could someone please share with me the exact syntax to use within Query
Analyzer for backing up a single table, rather than a whole database?
Thanks!
childofthe1980s
There isn't a T-SQL syntax for backing up a single table. May I ask why?
"childofthe1980s" wrote:

> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s
|||childofthe1980s wrote:
> Hello:
> Could someone please share with me the exact syntax to use within
> Query Analyzer for backing up a single table, rather than a whole
> database?
> Thanks!
> childofthe1980s
You can't backup a table, but you can backup a file or file group. See
"Using File Backups" in BOL for more information.
The other option is to schedule a DTS export on the table on a periodic
basis if you're more concerned with just the data in the table.
David G.
|||Hi,
If you need to backup this table frequently then,
1. Create a new file group
2. Put this table inside the new file group
3. Backup the file group. See file group backup in books online (see Backup
Database command)
If you need to backup this table only once then:-
1. Create a new database
2. From query analyzer run the below command
select * into newdb..tablename from sourcedb..tablename
3. Backup the new database.
Thanks
Hari
SQL Server MVP
"childofthe1980s" wrote:

> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s
|||Thanks, Hari! That last section about backing up the table once worked
perfectly!!!
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> If you need to backup this table frequently then,
> 1. Create a new file group
> 2. Put this table inside the new file group
> 3. Backup the file group. See file group backup in books online (see Backup
> Database command)
> If you need to backup this table only once then:-
> 1. Create a new database
> 2. From query analyzer run the below command
> select * into newdb..tablename from sourcedb..tablename
> 3. Backup the new database.
> Thanks
> Hari
> SQL Server MVP
>
> "childofthe1980s" wrote:
sql

backing up a single table

Hello:
Could someone please share with me the exact syntax to use within Query
Analyzer for backing up a single table, rather than a whole database?
Thanks!
childofthe1980sThere isn't a T-SQL syntax for backing up a single table. May I ask why?
"childofthe1980s" wrote:
> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||childofthe1980s wrote:
> Hello:
> Could someone please share with me the exact syntax to use within
> Query Analyzer for backing up a single table, rather than a whole
> database?
> Thanks!
> childofthe1980s
You can't backup a table, but you can backup a file or file group. See
"Using File Backups" in BOL for more information.
The other option is to schedule a DTS export on the table on a periodic
basis if you're more concerned with just the data in the table.
--
David G.|||Hi,
If you need to backup this table frequently then,
1. Create a new file group
2. Put this table inside the new file group
3. Backup the file group. See file group backup in books online (see Backup
Database command)
If you need to backup this table only once then:-
1. Create a new database
2. From query analyzer run the below command
select * into newdb..tablename from sourcedb..tablename
3. Backup the new database.
Thanks
Hari
SQL Server MVP
"childofthe1980s" wrote:
> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||Thanks, Hari! That last section about backing up the table once worked
perfectly!!!
"Hari Prasad" wrote:
> Hi,
> If you need to backup this table frequently then,
> 1. Create a new file group
> 2. Put this table inside the new file group
> 3. Backup the file group. See file group backup in books online (see Backup
> Database command)
> If you need to backup this table only once then:-
> 1. Create a new database
> 2. From query analyzer run the below command
> select * into newdb..tablename from sourcedb..tablename
> 3. Backup the new database.
> Thanks
> Hari
> SQL Server MVP
>
> "childofthe1980s" wrote:
> > Hello:
> >
> > Could someone please share with me the exact syntax to use within Query
> > Analyzer for backing up a single table, rather than a whole database?
> >
> > Thanks!
> >
> > childofthe1980s

backing up a single table

Hello:
Could someone please share with me the exact syntax to use within Query
Analyzer for backing up a single table, rather than a whole database?
Thanks!
childofthe1980sThere isn't a T-SQL syntax for backing up a single table. May I ask why?
"childofthe1980s" wrote:

> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||childofthe1980s wrote:
> Hello:
> Could someone please share with me the exact syntax to use within
> Query Analyzer for backing up a single table, rather than a whole
> database?
> Thanks!
> childofthe1980s
You can't backup a table, but you can backup a file or file group. See
"Using File Backups" in BOL for more information.
The other option is to schedule a DTS export on the table on a periodic
basis if you're more concerned with just the data in the table.
David G.|||Hi,
If you need to backup this table frequently then,
1. Create a new file group
2. Put this table inside the new file group
3. Backup the file group. See file group backup in books online (see Backup
Database command)
If you need to backup this table only once then:-
1. Create a new database
2. From query analyzer run the below command
select * into newdb..tablename from sourcedb..tablename
3. Backup the new database.
Thanks
Hari
SQL Server MVP
"childofthe1980s" wrote:

> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||Thanks, Hari! That last section about backing up the table once worked
perfectly!!!
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> If you need to backup this table frequently then,
> 1. Create a new file group
> 2. Put this table inside the new file group
> 3. Backup the file group. See file group backup in books online (see Backu
p
> Database command)
> If you need to backup this table only once then:-
> 1. Create a new database
> 2. From query analyzer run the below command
> select * into newdb..tablename from sourcedb..tablename
> 3. Backup the new database.
> Thanks
> Hari
> SQL Server MVP
>
> "childofthe1980s" wrote:
>

Backing up a file or table?

Is a file the same thing as a table?
Backup Database xxxx
file = 'whatever'
to somedevice
error:
file 'whatever' is not a database file for database 'xxxx'
'whatever' is a table in the database, but is it a file?
My goal is to backup only a few tables in a DB...not all of them.
Thanks,
Don
sql 7.0 or 2000
You can not backup at the table level, only DB, File or Filegroup. You
might want to BCP out the data from the table(s) in question if you don't
care about all the other data. You can however do periodic File or
Filegroup backups. That means you can create a new filegroup and place the
table(s) in that filegroup. Then you can backup that filegroup on a
different schedule than the rest of the db. But that does not mean you can
stop backing up the rest of the db or the logs. They are all required to do
a proper Filegroup backup or restore. See more details in BooksOnLine.
Andrew J. Kelly SQL MVP
"DonSQL2222" <DonSQL2222@.discussions.microsoft.com> wrote in message
news:FD970E6A-41E8-4C53-82E6-A7BAE70AFB47@.microsoft.com...
> Is a file the same thing as a table?
> Backup Database xxxx
> file = 'whatever'
> to somedevice
> error:
> file 'whatever' is not a database file for database 'xxxx'
> 'whatever' is a table in the database, but is it a file?
> My goal is to backup only a few tables in a DB...not all of them.
> Thanks,
> Don
> sql 7.0 or 2000
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

backgroundcolor in groups

Hello,
There are 3 groups in my table. The second and third group are hidden and
can be drilled down. I have a code to set alternate color for backgroundcolor
of each rows (the first group). The code is "= iif(RunningValue(
Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
However, some rows can not return alternate color. The report looks like that:
row1 (backgroundcolor is white)
row2 (backgroundcolor is yellow)
row3 (backgroundcolor is white)
row4 (backgroundcolor is white)
row5 (backgroundcolor is yellow)
Does anybody know how to set alternate color for groups?
Thanks!The RunningValue runs based on the details, but you are using the value to
determine the color of groups. If one group contains an even number of
details, the next group will have the same backgroundcolor as the previous
group.
At this point, there is no predefined aggregate function available that
would just count the groups. However, you may want to read this article
which explains the steps necessary to get this working:
http://blogs.msdn.com/chrishays/archive/2004/08/30/223068.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:C44CDF74-4505-47C7-B7EB-F53F788698EF@.microsoft.com...
> Hello,
> There are 3 groups in my table. The second and third group are hidden and
> can be drilled down. I have a code to set alternate color for
backgroundcolor
> of each rows (the first group). The code is "= iif(RunningValue(
> Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
> However, some rows can not return alternate color. The report looks like
that:
> row1 (backgroundcolor is white)
> row2 (backgroundcolor is yellow)
> row3 (backgroundcolor is white)
> row4 (backgroundcolor is white)
> row5 (backgroundcolor is yellow)
> Does anybody know how to set alternate color for groups?
> Thanks!
>
>|||Thanks for your help! It works after I change my code to
"= iif(RunningValue(Fields!uniqueID.Value & Fields!group1.Value,
CountDistinct, Nothing) mod 2, "white", "yellow")".
Jean
"Robert Bruckner [MSFT]" wrote:
> The RunningValue runs based on the details, but you are using the value to
> determine the color of groups. If one group contains an even number of
> details, the next group will have the same backgroundcolor as the previous
> group.
> At this point, there is no predefined aggregate function available that
> would just count the groups. However, you may want to read this article
> which explains the steps necessary to get this working:
> http://blogs.msdn.com/chrishays/archive/2004/08/30/223068.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:C44CDF74-4505-47C7-B7EB-F53F788698EF@.microsoft.com...
> > Hello,
> >
> > There are 3 groups in my table. The second and third group are hidden and
> > can be drilled down. I have a code to set alternate color for
> backgroundcolor
> > of each rows (the first group). The code is "= iif(RunningValue(
> > Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
> > However, some rows can not return alternate color. The report looks like
> that:
> >
> > row1 (backgroundcolor is white)
> > row2 (backgroundcolor is yellow)
> > row3 (backgroundcolor is white)
> > row4 (backgroundcolor is white)
> > row5 (backgroundcolor is yellow)
> >
> > Does anybody know how to set alternate color for groups?
> >
> > Thanks!
> >
> >
> >
> >
>
>sql

Background images won't show when exporting to Excel

Hello there,
I have a table with several cells that contain a value. Also for those cells
there's a specific background image, which is displayed depending on the
value contained (displayed) in the cell. Everything works fine, but when I
export the report to excel the background image is not displayed, only the
value.
Is there any way to achieve this ? (display and background image along with
the value?)
Is this a Excel's limitation or is it Reporting Services' ?
Is there any work around ?
Will the RS SP2 add this feature ?
Thanks in advance
CesarCesar;
this is an Excel limitation and backgroup images are not supported.
Unfortunatelly, there is no work around I am aware of.
"Cesar" wrote:
> Hello there,
> I have a table with several cells that contain a value. Also for those cells
> there's a specific background image, which is displayed depending on the
> value contained (displayed) in the cell. Everything works fine, but when I
> export the report to excel the background image is not displayed, only the
> value.
> Is there any way to achieve this ? (display and background image along with
> the value?)
> Is this a Excel's limitation or is it Reporting Services' ?
> Is there any work around ?
> Will the RS SP2 add this feature ?
> Thanks in advance
> Cesar

Background Colour with null values

I am trying to change the background colour of a textbox in a table to
display one colour if the value is null and another if there is a value. The
data is an integer type and when using isdbnull the colour displayed for the
null value columns is the colour for a value, not the null colour. If am
using '=nothing' it is displaying the null colour for the values of 0.
I am using RS 2000 SP2Try using the VB.NET syntax of 'is nothing' instead of '=nothing'
Matt A

Background Color Return:

I am writing a report that I need to return a background color for a cell in
a table. The background color depends on what color is named in another
cell. For example, if the cell (Quality_status.value) displays the word
Green then the cell (Quanity_status.value) background color needs to be
Green. I have tried several IIF expressions in the BackgroundColor
properties but none of them seem to work. Please help!!
--
Sincerely,
Debo
--
Sincerely,
DeboThe only supported property of a field is Value. Therefore you can't do it.
HTH
Charles Kangai, MCT, MCDBA
"Debo" wrote:
> I am writing a report that I need to return a background color for a cell in
> a table. The background color depends on what color is named in another
> cell. For example, if the cell (Quality_status.value) displays the word
> Green then the cell (Quanity_status.value) background color needs to be
> Green. I have tried several IIF expressions in the BackgroundColor
> properties but none of them seem to work. Please help!!
> --
> Sincerely,
> Debo
> --
> Sincerely,
> Debo|||I went to a textbox properties, background color, scrolled to the top to
add an expression. and did
=iif(fields!CompanyName.Value = "Alfred's","Red","Green") and it worked
have you tried
=(fields!Quality_status.Value)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Debo" <debo@.discussions.microsoft.com> wrote in message
news:835302E4-20F1-4D56-A738-9954ED6B346E@.microsoft.com...
> I am writing a report that I need to return a background color for a cell
in
> a table. The background color depends on what color is named in another
> cell. For example, if the cell (Quality_status.value) displays the word
> Green then the cell (Quanity_status.value) background color needs to be
> Green. I have tried several IIF expressions in the BackgroundColor
> properties but none of them seem to work. Please help!!
> --
> Sincerely,
> Debo
> --
> Sincerely,
> Debo|||Hi Wayne,
I thought Debo was trying to read the BackColor property, and you cannot
read any property of a textbox except Value. Of course if you enter the name
of the color in words in a textbox, then you can do it, because you are
reading the Value property.
Cheers
Charles Kangai, MCT, MCDBA
ps - good to hear from you Wayne!
"Wayne Snyder" wrote:
> I went to a textbox properties, background color, scrolled to the top to
> add an expression. and did
> =iif(fields!CompanyName.Value = "Alfred's","Red","Green") and it worked
> have you tried
> =(fields!Quality_status.Value)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
>
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Debo" <debo@.discussions.microsoft.com> wrote in message
> news:835302E4-20F1-4D56-A738-9954ED6B346E@.microsoft.com...
> > I am writing a report that I need to return a background color for a cell
> in
> > a table. The background color depends on what color is named in another
> > cell. For example, if the cell (Quality_status.value) displays the word
> > Green then the cell (Quanity_status.value) background color needs to be
> > Green. I have tried several IIF expressions in the BackgroundColor
> > properties but none of them seem to work. Please help!!
> > --
> > Sincerely,
> >
> > Debo
> > --
> > Sincerely,
> >
> > Debo
>
>

background color change in table

Hello,

I have a table with a large detail section where i display numeric information

like this

accounting info help discount common

15 20 6 17 23

10 6 9 22 8

1 6 2 7 45

...

...

...

if the list is one page long the data is difficult to read

is there a way to work with two background colors (yellow and white) and that

the background changes automatically (yellow white yellow white ....)

i know i have to work with an expression in my background property of my textcell or row

but i haven't figured out yet how to do the change (i think by odd an even rows ... but that's just

a guess).

Can anybody give me some info ...

Thanks already

Vincent

It's possilble. The expression you are looking for is described in the following post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=483177&SiteID=1

Good luck,

Simone

sql

Backcolor property

Is there a simple way to make the backcolor of a table
object alternate? If there was a row number global I could
just use an expression.Try something like this for the background color on the row...
=iif(RowNumber(Nothing) Mod 2, "white", "gainsboro")
"John Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:1f44601c4579a$fa975ba0$a501280a@.phx.gbl...
> Is there a simple way to make the backcolor of a table
> object alternate? If there was a row number global I could
> just use an expression.sql

Avoiding time-outs

The C++ application calls the database to look up property data. One
troublesome query is a function that returns a table, finding data which
is assembled from four or five tables through a view that has a join,
and then updating the resulting @.table from some other tables. There
are several queries inside the function, which are selected according
to which parameters are supplied (house #, street, zip, or perhaps parcel
number, or house #, street, town, city,...etc.). If a lot of parameters
are provided, and the property is not in the database, then several queries
may be attempted -- it keeps going until it runs out of queries or finds
something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
some failure cases, depending on the distribution of data. (~100 mil
properties in the DB) Some queires operate on the assumption the input data
is slightly faulty, and take relatively a long time, e.g., if WHERE
ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
all this is going on the application may decide the DB is never going to
return, and time out; it also seems more likely to throw an exception the
longer it has to wait. Is there a way to cause the DB function to fail if
it takes more than a certain amount of time? I could also recast it as
a procedure, and check the time consumed after every query, and abandon
the search if a certain amount of time has elapsed.

Thanks in advance,
Jim Geissmanjim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman

You don't give any information about your version of MSSQL, and the
client library you're using, but you may be able to set a suitable
timeout period on the client side. Alternatively, look at the "query
governor cost limit Option" in Books Online - this terminates queries
that run for more than a given number of seconds.

Simon|||jim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman

See "remote query timeout Option" in the help text. However, relying
on this may cause inconsistent bahaviour.

This design pattern can also lead to heavy load on your database.

As a suggestion, have two separate sets of queries, one that assumes
good data (should be much quicker which you want to use most times?)
and one that may have incorrect data (will be slower, but not used
very often). In you screen have a checkbox to indicate what search
option to use. Alternatively perform better validation on the data
before submitting the form.|||That sounds interesting. I'll look into it.

> You don't give any information about your version of MSSQL, and the
> client library you're using, but you may be able to set a suitable
> timeout period on the client side. Alternatively, look at the "query
> governor cost limit Option" in Books Online - this terminates queries
> that run for more than a given number of seconds.
> Simon

Avoiding Temporary Tables

Hello All,
I have a question about how I might avoid using a temporary table in a sql
query. In a query I am writing I am trying to extract data from a table o
n
an existing database via a set of identifier codes (These queries are writte
n
from the Matlab environment and the list of identifiers are easily accesible
in matlab).
The query I am currently using looks something like this...
select t.var1, t.code, t.var2
from pinf t
join
( SELECT distinct s.pcode as code
FROM prc s
WHERE s.code in (74156, 54471) ) -- identifiers 74156, 54471
as prctable on prctable.code = t.code
and '12/06/2005' >= t.dt1 and t.d2 <= '12/06/2005'
with the identifier being 74156, 54471. The problem is there is not a
convenient table with all of the identifiers in it. So the solution above
where I draw from an existing table is not sufficient.
I believe that I can create a temporary table with these identifiers in it,
but the performance of the query slows markedly.
Is there some other way to extract the data with these identifier from the
table pinf without creating a temporary table?
Any help would be greatly appreciated.
Cheers,
Lanny>> ... with the identifier being 74156, 54471.
Is this a set of two identifier values or a single comma separated
identifier value with two numbers in them?
I am not familiar with the matlab environment, but how exactly do you
extract the identifiers to the #temp table? The general alternatives ( which
in many cases may not be sufficient ) are using a view or even a base table
which can have all the required identifiers.
Anith|||It is a set of 2 distinct identifier, and conceptually I would want to look
for many more than 2 (perhaps on the order of thousands) of these distinct
identifiers.
I extract the identifiers via another query that I know functions properly
and will give me the desired identifier. I looked into using a base table
or a view, and did not believe they provided the functionality I needed.
Essentially, I have some list of numbers and I want to extract that list, bu
t
all of the elements of this list do not exist on any distinct table in the
database.
Thanks so much for the help.
"Anith Sen" wrote:

> Is this a set of two identifier values or a single comma separated
> identifier value with two numbers in them?
>
> I am not familiar with the matlab environment, but how exactly do you
> extract the identifiers to the #temp table? The general alternatives ( whi
ch
> in many cases may not be sufficient ) are using a view or even a base tabl
e
> which can have all the required identifiers.
> --
> Anith
>
>|||On Wed, 7 Dec 2005 10:18:03 -0800, Lanny wrote:

>It is a set of 2 distinct identifier, and conceptually I would want to look
>for many more than 2 (perhaps on the order of thousands) of these distinct
>identifiers.
>I extract the identifiers via another query that I know functions properly
>and will give me the desired identifier. I looked into using a base table
>or a view, and did not believe they provided the functionality I needed.
>Essentially, I have some list of numbers and I want to extract that list, b
ut
>all of the elements of this list do not exist on any distinct table in the
>database.
>Thanks so much for the help.
Hi Lanny,
I'm not sure if I understand your requirements completely, but based on
what I do understand, you might find what you need on Erlands site:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Avoiding temp tables

Dear all,
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:

>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan

avoiding accidental deletes...best practises?

Is there a way to get sql server to present a message box asking if you are you sure you want to delete all the data in a table.

also are there any 'best practises' to avoid accidental deletes.

For example Im making a lot of delete statements like:

del from table_name
where item_id = 23

but one time I executed this statement I mistakenly excuted the first line of the statment only, resulting in the loss of all the data in the table, luckily I was able to re-populate the table, but I wish to avoid that mistake in the future.

Let me ask this question: would you want a script stopping half-way through a stored procedure ?

To wait for a user dialog box to complete at 4am ?

If you are having problems with accidential deletes, consider investing in Lumigent log explorer (www.lumigent.com) or Red Gates Log Rescue (www.red-gate.com) - these tools will allow you to recover the data you deleted.

Regards,

DB007

|||

use it in a transaction ....

keep autocommit off....and keep ur logs (incase it still happens) to recover the data..

|||

Hi Airwalker,

The thing to remember is accidents will happen, and just to have processes in place to help clear up the mess afterwards.

Even with transactions (if used correctly) once committed the data is still deleted (accidentially, maliciously - by a hacker !).

The options available to you are : Recover directly from the transaction log (as per previous post), Restore a prior database backup and copy the data back into the live system, make the database read-only.

Now option 3 is unlikely as this system requires updates, but suitable for systems that are designed to be read-only.

Option 2 - takes a long time, even with page level restores available in SQL 2005 - and normally a significant amount of disk space (esp. if a copy of the database needs to be taken).

Option 1 - Usually involves the least amount of time, and can be performed offline whilst users are continuing to use the system.

Best Regards,

D

|||thanks for the advice guys...much appreciated.|||

If you are running SQL Server 2005, you might consider using SQL Server 2005 database snapshots. They are great for allowing you to very easily recover data that gets altered or deleted by mistakes like that.

You could have an Agent job that periodically creates a snapshot and gets rid of older snapshots. Then, you can pull data from the most recent snapshot to fix those kind of mistakes. Just be aware that DB snapshots are by no means a replacement for a good backup strategy.

http://glennberrysqlperformance.spaces.live.com

|||

one method i have adoped is .. from my application i can delete (for Eg) only one users roles (1-M) from UserROle table.. So i have created a Trigger which checks whether deleted table contains morethan one user if yes roll back and exist.. So from QA such delete statment can be prevented....

One more method is Just create a trigger in all table which checks Count(*) from deleted table and it is 0 then it means you are deleting all the rows... roll back and raiserror..

All these methods depends... it may or may not valid in all scenario...

try this script and do the necessary modificaiton

drop table test

create table test (col1 int)

insert into test select 1

insert into test select 2

insert into test select 3

insert into test select 4

insert into test select 5

insert into test select 6

insert into test select 1

insert into test select 1

alter trigger testtrig

on test for delete

as

declare @.i int

select @.i=count(*) from test

print @.i

if @.i=0

begin

print 'u r trying to delete blah...blah'

rollback tran

end

delete from test

delete from test where col1=1

select *From test

Madhu

Avoiding "Violation of Primary Key" with replication

I have a centralized database (server A), and two remote databases
(server B & C) all with a copy of the same database with a table with
user entered, primary key like ProductID. There is a possiblity that
the same ProductID gets entered into both B & C (which is ok).
I create a snapshot of B and replicate to A.
Is there a way to replicate C to server A and have replication ignore
any Primary Key violations during replication?
you can select the continue on data consistency profile. To do this right
click on your distribution agent and select agent profiles.
I would however advise you to come up with some partitioning scheme -
whether this involves adding another column to your pk with a location
specific parameter, or if you are using the identity property on you pk, go
to server b and type
DBCC Checkident ('YourTable',1000000)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<dkolva@.atlab.com> wrote in message
news:1110815869.167577.303520@.g14g2000cwa.googlegr oups.com...
> I have a centralized database (server A), and two remote databases
> (server B & C) all with a copy of the same database with a table with
> user entered, primary key like ProductID. There is a possiblity that
> the same ProductID gets entered into both B & C (which is ok).
> I create a snapshot of B and replicate to A.
> Is there a way to replicate C to server A and have replication ignore
> any Primary Key violations during replication?
>