How to determine space used by a file

Nags,
You could automate all of this using 'Performance
Condition' Alerts.
You can define a alert to be fired if the db size goes
above 3 GB (you have to put this number in KB's though).
As a response to the alert, you could setup a SQL Server
job to increase the database to appropriate size.
The only catch here is that you will have to be creative
in automatically increasing the size by 4 GB. Usually the
jobs I creat jobs that have a fixed db size specified.
You can also be notified when this alert fires and
increase db size in your script after the script has ran.
This will work the next time your db gets full. You will
also have to adjust the parameter for the db size (KB
value)
Hope this helps,
DeeJay

>--Original Message--
>How do I determine the space used in a file ? We have a
4 Gig file pre
>allocated. We want to be sure that there is enough space
in that. We want
>to run scripts to warn us if the space available in a
file group is less
>than one Gig. If it is less then 1 Gig we want to
allocate another 4 Gig
>file.
>-Nags
>
>.
>My Database is about 60 Gig with several filegroups. My problem is not to
preallocate too much and not to have space issues. We want to preallocate
to avoid disk defragmentation. I need to find out how much space is free in
a file, calculate the total free space in a file group and if it is < than 1
gig, only then add the new file. I hope you understand my requirement. Its
not the db size I am interested, I need to know the amount on data in each
file.
-Nags
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...[vbcol=seagreen]
> Nags,
> You could automate all of this using 'Performance
> Condition' Alerts.
> You can define a alert to be fired if the db size goes
> above 3 GB (you have to put this number in KB's though).
> As a response to the alert, you could setup a SQL Server
> job to increase the database to appropriate size.
> The only catch here is that you will have to be creative
> in automatically increasing the size by 4 GB. Usually the
> jobs I creat jobs that have a fixed db size specified.
> You can also be notified when this alert fires and
> increase db size in your script after the script has ran.
> This will work the next time your db gets full. You will
> also have to adjust the parameter for the db size (KB
> value)
>
> Hope this helps,
> DeeJay
>
> 4 Gig file pre
> in that. We want
> file group is less
> allocate another 4 Gig|||Adding new files in response to low free space in the db is a very bad idea.
SQL Server uses a proportional fill algorithm that is based on the amount of
free space in each file in the filegroup. The ideal situation would be to
have multiple files all of the same size with the same amount of free space.
In your case if you added a new file it would put most of the new data in
the new file and defeat some of the purpose of spreading the data over
multiple files. What you should do is manually (or script not autogrow)
grow each existing file so they all are always the same size.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> My Database is about 60 Gig with several filegroups. My problem is not to
> preallocate too much and not to have space issues. We want to preallocate
> to avoid disk defragmentation. I need to find out how much space is free
in
> a file, calculate the total free space in a file group and if it is < than
1
> gig, only then add the new file. I hope you understand my requirement.
Its
> not the db size I am interested, I need to know the amount on data in each
> file.
> -Nags
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
>|||Let me explain on how we are managing our disk space and file groups more
clearly.
What we had before
- we have at present taken size estimations of all our current tables and
our database size.
- production database is increasing by about 50 % every year based on 3 year
data
- we had disk contention
- we had performance issues because of this
What we did to avoid it
- we have 3 major tables which we have put them in individual file group and
their indexes in individual file group
- we moved all transactions tables (only inserts) into a separate file group
and their indexes into another file group
- we moved all major accessed tables into a separate file group and their
indexes into another file group
- we moved all other tables into a separate file group and their indexes
into another file group
- we distributed these file groups onto 4 disk volumes, two volumes are 3
disk raid 5 and 2 volumes are disk mirrored.
- each file group has been allocated twice the space required
- each file has been created with 4 Gig
- file groups have been created so that we can move individual file groups
to different disk sets when volume grows and we need better performance
- each file has been created with 4 Gig so that disk space is preallocated
and does not expand as required, which might cause disk defragmentation
- we are collecting statistics periodically to find out which disk drive has
contention, and if we find it, we will be putting new volumes and moving the
file groups on to them
- each file group is made of multiple files
The requirement is to find out if any of the file group is running out of
space. In short I want to determine how much is being used and how much
space is free within a file. Can you give me this info please ?
-Nags
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Adding new files in response to low free space in the db is a very bad
idea.
> SQL Server uses a proportional fill algorithm that is based on the amount
of
> free space in each file in the filegroup. The ideal situation would be to
> have multiple files all of the same size with the same amount of free
space.
> In your case if you added a new file it would put most of the new data in
> the new file and defeat some of the purpose of spreading the data over
> multiple files. What you should do is manually (or script not autogrow)
> grow each existing file so they all are always the same size.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
to[vbcol=seagreen]
preallocate[vbcol=seagreen]
free[vbcol=seagreen]
> in
than[vbcol=seagreen]
> 1
> Its
each[vbcol=seagreen]
>|||A 3 disk RAID 5 is about the worst performing RAID there is. You have
probably made much more work than necessary and certainly do not have an
optimum setup. I would venture to guess you could take those 10 disks and
make a RAID 10 with them instead. Then you could place all the files on the
RAID 10 and would probably get better performance than spreading it out like
you have. This is assuming you already have the logs on their own RAID 1.
If your getting a lot of disk contention you might also ensure you have
enough ram as well.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
> Let me explain on how we are managing our disk space and file groups more
> clearly.
> What we had before
> - we have at present taken size estimations of all our current tables and
> our database size.
> - production database is increasing by about 50 % every year based on 3
year
> data
> - we had disk contention
> - we had performance issues because of thisly made more work athn
> What we did to avoid it
> - we have 3 major tables which we have put them in individual file group
and
> their indexes in individual file group
> - we moved all transactions tables (only inserts) into a separate file
group
> and their indexes into another file group
> - we moved all major accessed tables into a separate file group and their
> indexes into another file group
> - we moved all other tables into a separate file group and their indexes
> into another file group
> - we distributed these file groups onto 4 disk volumes, two volumes are 3
> disk raid 5 and 2 volumes are disk mirrored.
> - each file group has been allocated twice the space required
> - each file has been created with 4 Gig
> - file groups have been created so that we can move individual file groups
> to different disk sets when volume grows and we need better performance
> - each file has been created with 4 Gig so that disk space is preallocated
> and does not expand as required, which might cause disk defragmentation
> - we are collecting statistics periodically to find out which disk drive
has
> contention, and if we find it, we will be putting new volumes and moving
the
> file groups on to them
> - each file group is made of multiple files
> The requirement is to find out if any of the file group is running out of
> space. In short I want to determine how much is being used and how much
> space is free within a file. Can you give me this info please ?
>
> -Nags
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> idea.
amount[vbcol=seagreen]
> of
to[vbcol=seagreen]
> space.
in[vbcol=seagreen]
not[vbcol=seagreen]
> to
> preallocate
> free
> than
requirement.[vbcol=seagreen]
> each
>|||Nags
>- we have 3 major tables which we have put them in >individual file group
and
>their indexes in individual file group
Do you have separate filegroups for the tables and their indexes?
If they are in separate filegroups, then you cannot back up and restore them
as a single unit
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eLYz5gnJEHA.3688@.TK2MSFTNGP10.phx.gbl...
> A 3 disk RAID 5 is about the worst performing RAID there is. You have
> probably made much more work than necessary and certainly do not have an
> optimum setup. I would venture to guess you could take those 10 disks and
> make a RAID 10 with them instead. Then you could place all the files on
the
> RAID 10 and would probably get better performance than spreading it out
like
> you have. This is assuming you already have the logs on their own RAID 1.
> If your getting a lot of disk contention you might also ensure you have
> enough ram as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
and[vbcol=seagreen]
> year
> and
> group
their[vbcol=seagreen]
3[vbcol=seagreen]
groups[vbcol=seagreen]
preallocated[vbcol=seagreen]
> has
> the
of[vbcol=seagreen]
> amount
be[vbcol=seagreen]
> to
> in
autogrow)[vbcol=seagreen]
> not
<[vbcol=seagreen]
> requirement.
in[vbcol=seagreen]
>

How to determine space used by a file

Nags,
You could automate all of this using 'Performance
Condition' Alerts.
You can define a alert to be fired if the db size goes
above 3 GB (you have to put this number in KB's though).
As a response to the alert, you could setup a SQL Server
job to increase the database to appropriate size.
The only catch here is that you will have to be creative
in automatically increasing the size by 4 GB. Usually the
jobs I creat jobs that have a fixed db size specified.
You can also be notified when this alert fires and
increase db size in your script after the script has ran.
This will work the next time your db gets full. You will
also have to adjust the parameter for the db size (KB
value)
Hope this helps,
DeeJay

>--Original Message--
>How do I determine the space used in a file ? We have a
4 Gig file pre
>allocated. We want to be sure that there is enough space
in that. We want
>to run scripts to warn us if the space available in a
file group is less
>than one Gig. If it is less then 1 Gig we want to
allocate another 4 Gig
>file.
>-Nags
>
>.
>
My Database is about 60 Gig with several filegroups. My problem is not to
preallocate too much and not to have space issues. We want to preallocate
to avoid disk defragmentation. I need to find out how much space is free in
a file, calculate the total free space in a file group and if it is < than 1
gig, only then add the new file. I hope you understand my requirement. Its
not the db size I am interested, I need to know the amount on data in each
file.
-Nags
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...[vbcol=seagreen]
> Nags,
> You could automate all of this using 'Performance
> Condition' Alerts.
> You can define a alert to be fired if the db size goes
> above 3 GB (you have to put this number in KB's though).
> As a response to the alert, you could setup a SQL Server
> job to increase the database to appropriate size.
> The only catch here is that you will have to be creative
> in automatically increasing the size by 4 GB. Usually the
> jobs I creat jobs that have a fixed db size specified.
> You can also be notified when this alert fires and
> increase db size in your script after the script has ran.
> This will work the next time your db gets full. You will
> also have to adjust the parameter for the db size (KB
> value)
>
> Hope this helps,
> DeeJay
> 4 Gig file pre
> in that. We want
> file group is less
> allocate another 4 Gig
|||Adding new files in response to low free space in the db is a very bad idea.
SQL Server uses a proportional fill algorithm that is based on the amount of
free space in each file in the filegroup. The ideal situation would be to
have multiple files all of the same size with the same amount of free space.
In your case if you added a new file it would put most of the new data in
the new file and defeat some of the purpose of spreading the data over
multiple files. What you should do is manually (or script not autogrow)
grow each existing file so they all are always the same size.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> My Database is about 60 Gig with several filegroups. My problem is not to
> preallocate too much and not to have space issues. We want to preallocate
> to avoid disk defragmentation. I need to find out how much space is free
in
> a file, calculate the total free space in a file group and if it is < than
1
> gig, only then add the new file. I hope you understand my requirement.
Its
> not the db size I am interested, I need to know the amount on data in each
> file.
> -Nags
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
>
|||Let me explain on how we are managing our disk space and file groups more
clearly.
What we had before
- we have at present taken size estimations of all our current tables and
our database size.
- production database is increasing by about 50 % every year based on 3 year
data
- we had disk contention
- we had performance issues because of this
What we did to avoid it
- we have 3 major tables which we have put them in individual file group and
their indexes in individual file group
- we moved all transactions tables (only inserts) into a separate file group
and their indexes into another file group
- we moved all major accessed tables into a separate file group and their
indexes into another file group
- we moved all other tables into a separate file group and their indexes
into another file group
- we distributed these file groups onto 4 disk volumes, two volumes are 3
disk raid 5 and 2 volumes are disk mirrored.
- each file group has been allocated twice the space required
- each file has been created with 4 Gig
- file groups have been created so that we can move individual file groups
to different disk sets when volume grows and we need better performance
- each file has been created with 4 Gig so that disk space is preallocated
and does not expand as required, which might cause disk defragmentation
- we are collecting statistics periodically to find out which disk drive has
contention, and if we find it, we will be putting new volumes and moving the
file groups on to them
- each file group is made of multiple files
The requirement is to find out if any of the file group is running out of
space. In short I want to determine how much is being used and how much
space is free within a file. Can you give me this info please ?
-Nags
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Adding new files in response to low free space in the db is a very bad
idea.
> SQL Server uses a proportional fill algorithm that is based on the amount
of
> free space in each file in the filegroup. The ideal situation would be to
> have multiple files all of the same size with the same amount of free
space.[vbcol=seagreen]
> In your case if you added a new file it would put most of the new data in
> the new file and defeat some of the purpose of spreading the data over
> multiple files. What you should do is manually (or script not autogrow)
> grow each existing file so they all are always the same size.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
to[vbcol=seagreen]
preallocate[vbcol=seagreen]
free[vbcol=seagreen]
> in
than[vbcol=seagreen]
> 1
> Its
each
>
|||A 3 disk RAID 5 is about the worst performing RAID there is. You have
probably made much more work than necessary and certainly do not have an
optimum setup. I would venture to guess you could take those 10 disks and
make a RAID 10 with them instead. Then you could place all the files on the
RAID 10 and would probably get better performance than spreading it out like
you have. This is assuming you already have the logs on their own RAID 1.
If your getting a lot of disk contention you might also ensure you have
enough ram as well.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
> Let me explain on how we are managing our disk space and file groups more
> clearly.
> What we had before
> - we have at present taken size estimations of all our current tables and
> our database size.
> - production database is increasing by about 50 % every year based on 3
year
> data
> - we had disk contention
> - we had performance issues because of thisly made more work athn
> What we did to avoid it
> - we have 3 major tables which we have put them in individual file group
and
> their indexes in individual file group
> - we moved all transactions tables (only inserts) into a separate file
group
> and their indexes into another file group
> - we moved all major accessed tables into a separate file group and their
> indexes into another file group
> - we moved all other tables into a separate file group and their indexes
> into another file group
> - we distributed these file groups onto 4 disk volumes, two volumes are 3
> disk raid 5 and 2 volumes are disk mirrored.
> - each file group has been allocated twice the space required
> - each file has been created with 4 Gig
> - file groups have been created so that we can move individual file groups
> to different disk sets when volume grows and we need better performance
> - each file has been created with 4 Gig so that disk space is preallocated
> and does not expand as required, which might cause disk defragmentation
> - we are collecting statistics periodically to find out which disk drive
has
> contention, and if we find it, we will be putting new volumes and moving
the[vbcol=seagreen]
> file groups on to them
> - each file group is made of multiple files
> The requirement is to find out if any of the file group is running out of
> space. In short I want to determine how much is being used and how much
> space is free within a file. Can you give me this info please ?
>
> -Nags
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> idea.
amount[vbcol=seagreen]
> of
to[vbcol=seagreen]
> space.
in[vbcol=seagreen]
not[vbcol=seagreen]
> to
> preallocate
> free
> than
requirement.
> each
>
|||Nags
>- we have 3 major tables which we have put them in >individual file group
and
>their indexes in individual file group
Do you have separate filegroups for the tables and their indexes?
If they are in separate filegroups, then you cannot back up and restore them
as a single unit
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eLYz5gnJEHA.3688@.TK2MSFTNGP10.phx.gbl...
> A 3 disk RAID 5 is about the worst performing RAID there is. You have
> probably made much more work than necessary and certainly do not have an
> optimum setup. I would venture to guess you could take those 10 disks and
> make a RAID 10 with them instead. Then you could place all the files on
the
> RAID 10 and would probably get better performance than spreading it out
like[vbcol=seagreen]
> you have. This is assuming you already have the logs on their own RAID 1.
> If your getting a lot of disk contention you might also ensure you have
> enough ram as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
and[vbcol=seagreen]
> year
> and
> group
their[vbcol=seagreen]
3[vbcol=seagreen]
groups[vbcol=seagreen]
preallocated[vbcol=seagreen]
> has
> the
of[vbcol=seagreen]
> amount
be[vbcol=seagreen]
> to
> in
autogrow)[vbcol=seagreen]
> not
<[vbcol=seagreen]
> requirement.
in
>

how to delete all the data in the sql server?

Hi,
Is there a way that i can delete all the data from
all the tables in the sql server, i want to remain the
table's structure and user define function and i dun want
to delete the whole database.
Thank you.
regards,
florenceNo command per se. You'd have to execute a DELETE or TRUNCATE TABLE
statement against all tables. Such is easy to generate or automate (using a
cursor), however you have to consider foreign keys so you do it in the right
order. Or you can run the statements several times.
I suggest that you generate script for your DDL. You really should have your
DDL in a script file (or one file per object) anyhow, so you can generate
script, tidy up and comment the script. You can then drop the database and
re-create it from that script file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Hi Florence.
You might find it easier to simply script the database schema (including
stored procs, functions etc), using the SQL Enterprise Manager's scripting
tools, then dropping the database & creating an empty one from the script.
The bigger the database, the more effective this approach will be.
Regards,
Greg Linwood
SQL Server MVP
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Hi,
Execute this command in SQL Analyzer, Replace dbname with your database
name. This commands will delete all the data from all the user tables.
Careful !!! Truncate is not a logged operation, After issuing this command
you will not be able rollback the data.
use dbname
go
declare @.x nvarchar(255)
set @.x=''
select @.x = @.x+' truncate table ' + name
from sysobjects where type='U'
exec sp_executesql @.x
go
Thanks
Hari
MCDBA
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Truncate table won't work for tables that are referred to by foreign keys.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
Truncate table is logged, and can be rolled back:
use tempdb
go
create table t(c1 int identity)
insert t default values
insert t default values
insert t default values
go
select * from t
begin tran
truncate table t
select * from t
rollback
select * from t
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute this command in SQL Analyzer, Replace dbname with your database
> name. This commands will delete all the data from all the user tables.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
>
> use dbname
> go
> declare @.x nvarchar(255)
> set @.x=''
> select @.x = @.x+' truncate table ' + name
> from sysobjects where type='U'
> exec sp_executesql @.x
> go
> Thanks
> Hari
> MCDBA
>
> "florence" <florencelee@.visualsolutions.co.my> wrote in message
> news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > Hi,
> >
> > Is there a way that i can delete all the data from
> > all the tables in the sql server, i want to remain the
> > table's structure and user define function and i dun want
> > to delete the whole database.
> >
> > Thank you.
> >
> > regards,
> >
> > florence
>|||You may want to generate a script to first drop all the foreign keys and
generate another script to then add the foreign keys back. Run the first
script before the TRUNCATE TABLE statements, and then run the second script
afterwards.
Also, I'd probably re-write the script as follows (with some minor changes):
use dbname
go
declare @.x varchar(8000)
set @.x=''
select @.x = @.x+' truncate table ' + QUOTENAME(name) + char(13) + char(10)
from sysobjects where type='U'
exec sp_executesql @.x
go
Note that varchar(8000) still may not be enough.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute this command in SQL Analyzer, Replace dbname with your database
> name. This commands will delete all the data from all the user tables.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
>
> use dbname
> go
> declare @.x nvarchar(255)
> set @.x=''
> select @.x = @.x+' truncate table ' + name
> from sysobjects where type='U'
> exec sp_executesql @.x
> go
> Thanks
> Hari
> MCDBA
>
> "florence" <florencelee@.visualsolutions.co.my> wrote in message
> news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > Hi,
> >
> > Is there a way that i can delete all the data from
> > all the tables in the sql server, i want to remain the
> > table's structure and user define function and i dun want
> > to delete the whole database.
> >
> > Thank you.
> >
> > regards,
> >
> > florence
>|||Thanks a lot Tiber for the information.
I was under the impression that DDL will automatically commit the
transactions.
Thanks
Hari
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OYvZ3rtpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> Truncate table won't work for tables that are referred to by foreign keys.
>
> > Careful !!! Truncate is not a logged operation, After issuing this
command
> > you will not be able rollback the data.
> Truncate table is logged, and can be rolled back:
> use tempdb
> go
> create table t(c1 int identity)
> insert t default values
> insert t default values
> insert t default values
> go
> select * from t
> begin tran
> truncate table t
> select * from t
> rollback
> select * from t
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Execute this command in SQL Analyzer, Replace dbname with your database
> > name. This commands will delete all the data from all the user tables.
> >
> > Careful !!! Truncate is not a logged operation, After issuing this
command
> > you will not be able rollback the data.
> >
> >
> > use dbname
> > go
> > declare @.x nvarchar(255)
> > set @.x=''
> > select @.x = @.x+' truncate table ' + name
> > from sysobjects where type='U'
> > exec sp_executesql @.x
> > go
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "florence" <florencelee@.visualsolutions.co.my> wrote in message
> > news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > > Hi,
> > >
> > > Is there a way that i can delete all the data from
> > > all the tables in the sql server, i want to remain the
> > > table's structure and user define function and i dun want
> > > to delete the whole database.
> > >
> > > Thank you.
> > >
> > > regards,
> > >
> > > florence
> >
> >
>

How to defne a globale variable in the report

I want's to define some global contant variable which contains some values

Like Name="Abc"

Age=123

Date=12/12/2006

How can i define it on sql server reporting serices report and access it value to assign on some fields?

You could use hidden parameters with default values I guess.

1 - Add a parameter to your report and set it to the correct type (string / number etc) and mark it as hidden

2 - Set the default value that you want for it

3 - In your report, access it with something like "=Parameters!theParameter.Value"

Regards Andreas

|||

On the report properties, click on the Code tab and define it there, e.g.

Public Dim Age as Integer = 123

Then, you can reference it in the report as =Code.Age

|||

Which approach will be best for performance?

Can anybody give the pros and cons of the both approaches .

|||

If you want to be able to change the defined variables when calling a report it's good to have them as parameters. Lets say you programatically calls the report because you want to render a report in a WinForms app or something and want to be able to set the parameters from the winForms app, then the hidden parameter way is a good way I guess.

Otherwise, I suggest you do as Teo says.

Regards Andreas

|||

hi,

but parameters are readonly.

You cant set them through your custom code.

hemant

How to defne a globale variable in the report

I want's to define some global contant variable which contains some values

Like Name="Abc"

Age=123

Date=12/12/2006

How can i define it on sql server reporting serices report and access it value to assign on some fields?

You could use hidden parameters with default values I guess.

1 - Add a parameter to your report and set it to the correct type (string / number etc) and mark it as hidden

2 - Set the default value that you want for it

3 - In your report, access it with something like "=Parameters!theParameter.Value"

Regards Andreas

|||

On the report properties, click on the Code tab and define it there, e.g.

Public Dim Age as Integer = 123

Then, you can reference it in the report as =Code.Age

|||

Which approach will be best for performance?

Can anybody give the pros and cons of the both approaches .

|||

If you want to be able to change the defined variables when calling a report it's good to have them as parameters. Lets say you programatically calls the report because you want to render a report in a WinForms app or something and want to be able to set the parameters from the winForms app, then the hidden parameter way is a good way I guess.

Otherwise, I suggest you do as Teo says.

Regards Andreas

|||

hi,

but parameters are readonly.

You cant set them through your custom code.

hemant

How to define token syntax in MSSQL2005 sp1?

There is one token in my Agent Job $WMI(DatabaseName)

Now,I defined this using $(ESCAPE_NONE(WMI(DatabaseName)))

but failed and prompted: Variable WMI(DatabaseName) not found

What should i do for this? thanks

From the updated books online:

For jobs that run in response to WMI alerts, the value of the property specified by property. For example, $(WMI(DatabaseName)) provides the value of the DatabaseName property for the WMI event that caused the alert to run.

So I believe you don't need the ESCAPE_NONE...

|||

If do not add ESCAPE_NONE,prompt "For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run"

Discover In SQL Server 2005 SP1, the SQL Server Agent job step token syntax has changed

url：http://support.microsoft.com/kb/915845

I want to do something for each database just was created,So I could not specifiy the value of database, how to deal with that? thanks

|||

Perhaps the tokens remain disabled?

Because access to Eventlog is not always secured, the alerts are disabled by default. To get the substitutions to work, you should ensure that only members of trusted groups have write permissions to Eventlog, then enable these tokens on the Agent Properties Dialog Alert System tab, or you can set the AlertReplaceRuntimeTokens reg key.

jkh

How to define token syntax in MSSQL2005 sp1?

There is one token in my Agent Job $WMI(DatabaseName)

Now,I defined this using $(ESCAPE_NONE(WMI(DatabaseName)))

but failed and prompted: Variable WMI(DatabaseName) not found

What should i do for this? thanks

From the updated books online:

For jobs that run in response to WMI alerts, the value of the property specified by property. For example, $(WMI(DatabaseName)) provides the value of the DatabaseName property for the WMI event that caused the alert to run.

So I believe you don't need the ESCAPE_NONE...

|||

If do not add ESCAPE_NONE,prompt "For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run"

Discover In SQL Server 2005 SP1, the SQL Server Agent job step token syntax has changed

url：http://support.microsoft.com/kb/915845

I want to do something for each database just was created,So I could not specifiy the value of database, how to deal with that? thanks

|||

Perhaps the tokens remain disabled?

Because access to Eventlog is not always secured, the alerts are disabled by default. To get the substitutions to work, you should ensure that only members of trusted groups have write permissions to Eventlog, then enable these tokens on the Agent Properties Dialog Alert System tab, or you can set the AlertReplaceRuntimeTokens reg key.

jkh

How to define the XML schema for a SQLXMLBulkLoad

Hello everyone. I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
have a question regarding how to define an xml-schema to bulk load an
XML document that I get from our system.
Here's what the XML document looks like:
<CatalogDelta>
<RevisionID>1.0</RevisionID>
<CatalogVersion>195</CatalogVersion>
<deletes>
<album id="123" />
<song id="2345" />
<song id="4563" />
</deletes>
</CatalogDelta>
What I want to do is get the xml bulk loaded into three SQL database
tables:
Table CatalogDelta
( RevisionID varchar(50),
CatalogVersion varchar(50)
)
1.0 | 195
Table album_deletes
( id varchar(50) )
123
table song_deletes
( id varchar(50) )
2345
4563
Here's the XML-Schema I've been trying to use but can't seem to get
past the <deletes> element.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="RevisionID" sql:field="revision_id"
sql:datatype="nvarchar(50)" />
<xsd:element name="CatalogVersion" sql:field="catalog_version"
sql:datatype="nvarchar(50)" />
<!-- CatalogDelta -->
<xsd:group name="DeltaCatalogGroup">
<xsd:sequence>
<xsd:element ref="RevisionID"/>
<xsd:element ref="CatalogVersion" />
</xsd:sequence>
</xsd:group>
<xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
<xsd:complexType>
<xsd:group ref="DeltaCatalogGroup"/>
</xsd:complexType>
</xsd:element>
<!-- Deletes -->
<xsd:element name="deletes" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<!-- Delete Albums -->
<xsd:element name="album" sql:relation="album_deletes">
<xsd:complexType>
<xsd:attribute name="id" sql:field="album_id"
type="xsd:string" sql:datatype="nvarchar(05)" />
</xsd:complexType>
</xsd:element>
<!-- Delete Songs -->
<xsd:element name="song" sql:relation="song_deletes">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string"
sql:field="song_id" sql:datatype="nvarchar(50)" />
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
</xsd:element>
</schema>
When I bulk load using this schema, the contents get put into the
CatalogDelta table but nothing gets put into the album_deletes or
song_deletes tables.
Can someone point out what I am doing wrong, please?
Thank you for your help.
Sincerely
Steve Cummings
Hello,
Whenever you have children you have to define relationship in the schema:
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="catalog_album"
parent="CatalogDelta"
child="album_deletes"
parent-key="?"
child-key="?"/>
</xsd:appinfo>
</xsd:annotation>
Then add the deletes element to the Catalog:
<xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
<xsd:complexType>
<xsd:sequence>
<xsd:group ref="DeltaCatalogGroup"/>
<xsd:element ref="deletes" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="deletes" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<xsd:element name="album" sql:relation="album_deletes"
sql:relationship="catalog_album">
<xsd:complexType>
<xsd:attribute name="id" sql:field="album_id"
type="xsd:string" sql:datatype="nvarchar(05)" />
</xsd:complexType>
</xsd:element>
You need to figure out how the tables related to each other and describe
this in the schema.
Take a look at
this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
I hope this helps.
Regards,
Monica Frintu
"sgcummings@.sbcglobal.net" wrote:

> Hello everyone. I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
> have a question regarding how to define an xml-schema to bulk load an
> XML document that I get from our system.
> Here's what the XML document looks like:
> <CatalogDelta>
> <RevisionID>1.0</RevisionID>
> <CatalogVersion>195</CatalogVersion>
> <deletes>
> <album id="123" />
> <song id="2345" />
> <song id="4563" />
> </deletes>
> </CatalogDelta>
> What I want to do is get the xml bulk loaded into three SQL database
> tables:
> Table CatalogDelta
> ( RevisionID varchar(50),
> CatalogVersion varchar(50)
> )
> 1.0 | 195
> Table album_deletes
> ( id varchar(50) )
> 123
> table song_deletes
> ( id varchar(50) )
> 2345
> 4563
> Here's the XML-Schema I've been trying to use but can't seem to get
> past the <deletes> element.
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="RevisionID" sql:field="revision_id"
> sql:datatype="nvarchar(50)" />
> <xsd:element name="CatalogVersion" sql:field="catalog_version"
> sql:datatype="nvarchar(50)" />
> <!-- CatalogDelta -->
> <xsd:group name="DeltaCatalogGroup">
> <xsd:sequence>
> <xsd:element ref="RevisionID"/>
> <xsd:element ref="CatalogVersion" />
> </xsd:sequence>
> </xsd:group>
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
> <xsd:group ref="DeltaCatalogGroup"/>
> </xsd:complexType>
> </xsd:element>
> <!-- Deletes -->
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <!-- Delete Albums -->
> <xsd:element name="album" sql:relation="album_deletes">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
> <!-- Delete Songs -->
> <xsd:element name="song" sql:relation="song_deletes">
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string"
> sql:field="song_id" sql:datatype="nvarchar(50)" />
> </xsd:complexType>
> </xsd:element>
> </xsd:all>
> </xsd:complexType>
> </xsd:element>
> </schema>
> When I bulk load using this schema, the contents get put into the
> CatalogDelta table but nothing gets put into the album_deletes or
> song_deletes tables.
> Can someone point out what I am doing wrong, please?
> Thank you for your help.
> Sincerely
> Steve Cummings
>
|||On Apr 10, 2:54 pm, Monica Frintu [MSFT]
<MonicaFrintuM...@.discussions.microsoft.com> wrote:
> Hello,
> Whenever you have children you have todefinerelationship in theschema:
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="catalog_album"
> parent="CatalogDelta"
> child="album_deletes"
> parent-key="?"
> child-key="?"/>
> </xsd:appinfo>
> </xsd:annotation>
> Then add the deletes element to the Catalog:
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:group ref="DeltaCatalogGroup"/>
> <xsd:element ref="deletes" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <xsd:element name="album" sql:relation="album_deletes"
> sql:relationship="catalog_album">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
> You need to figure out how the tables related to each other and describe
> this in theschema.
> Take a look at
> this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
> I hope this helps.
> Regards,
> Monica Frintu
>
> "sgcummi...@.sbcglobal.net" wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hello Monica!
Thank you for the information. It was very helpful. I was able to
extrapolate from what you provided and have everything working now.
It only took about 20 minutes to complete all the adjustments to get
the schema bulk load to work.
With much appreciation for your help.
Steve Cummings

How to define the XML schema for a SQLXMLBulkLoad

Hello everyone. I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
have a question regarding how to define an xml-schema to bulk load an
XML document that I get from our system.
Here's what the XML document looks like:
<CatalogDelta>
<RevisionID>1.0</RevisionID>
<CatalogVersion>195</CatalogVersion>
<deletes>
<album id="123" />
<song id="2345" />
<song id="4563" />
</deletes>
</CatalogDelta>
What I want to do is get the xml bulk loaded into three SQL database
tables:
Table CatalogDelta
( RevisionID varchar(50),
CatalogVersion varchar(50)
)
1.0 | 195
Table album_deletes
( id varchar(50) )
123
table song_deletes
( id varchar(50) )
2345
4563
Here's the XML-Schema I've been trying to use but can't seem to get
past the <deletes> element.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="RevisionID" sql:field="revision_id"
sql:datatype="nvarchar(50)" />
<xsd:element name="CatalogVersion" sql:field="catalog_version"
sql:datatype="nvarchar(50)" />
<!-- CatalogDelta -->
<xsd:group name="DeltaCatalogGroup">
<xsd:sequence>
<xsd:element ref="RevisionID"/>
<xsd:element ref="CatalogVersion" />
</xsd:sequence>
</xsd:group>
<xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
<xsd:complexType>
<xsd:group ref="DeltaCatalogGroup"/>
</xsd:complexType>
</xsd:element>
<!-- Deletes -->
<xsd:element name="deletes" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<!-- Delete Albums -->
<xsd:element name="album" sql:relation="album_deletes">
<xsd:complexType>
<xsd:attribute name="id" sql:field="album_id"
type="xsd:string" sql:datatype="nvarchar(05)" />
</xsd:complexType>
</xsd:element>
<!-- Delete Songs -->
<xsd:element name="song" sql:relation="song_deletes">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string"
sql:field="song_id" sql:datatype="nvarchar(50)" />
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
</xsd:element>
</schema>
When I bulk load using this schema, the contents get put into the
CatalogDelta table but nothing gets put into the album_deletes or
song_deletes tables.
Can someone point out what I am doing wrong, please?
Thank you for your help.
Sincerely
Steve CummingsHello,
Whenever you have children you have to define relationship in the schema:
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="catalog_album"
parent="CatalogDelta"
child="album_deletes"
parent-key="?"
child-key="?"/>
</xsd:appinfo>
</xsd:annotation>
Then add the deletes element to the Catalog:
<xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
<xsd:complexType>
<xsd:sequence>
<xsd:group ref="DeltaCatalogGroup"/>
<xsd:element ref="deletes" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="deletes" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<xsd:element name="album" sql:relation="album_deletes"
sql:relationship="catalog_album">
<xsd:complexType>
<xsd:attribute name="id" sql:field="album_id"
type="xsd:string" sql:datatype="nvarchar(05)" />
</xsd:complexType>
</xsd:element>
You need to figure out how the tables related to each other and describe
this in the schema.
Take a look at
this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
I hope this helps.
Regards,
Monica Frintu
"sgcummings@.sbcglobal.net" wrote:

> Hello everyone. I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
> have a question regarding how to define an xml-schema to bulk load an
> XML document that I get from our system.
> Here's what the XML document looks like:
> <CatalogDelta>
> <RevisionID>1.0</RevisionID>
> <CatalogVersion>195</CatalogVersion>
> <deletes>
> <album id="123" />
> <song id="2345" />
> <song id="4563" />
> </deletes>
> </CatalogDelta>
> What I want to do is get the xml bulk loaded into three SQL database
> tables:
> Table CatalogDelta
> ( RevisionID varchar(50),
> CatalogVersion varchar(50)
> )
> 1.0 | 195
> Table album_deletes
> ( id varchar(50) )
> 123
> table song_deletes
> ( id varchar(50) )
> 2345
> 4563
> Here's the XML-Schema I've been trying to use but can't seem to get
> past the <deletes> element.
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="RevisionID" sql:field="revision_id"
> sql:datatype="nvarchar(50)" />
> <xsd:element name="CatalogVersion" sql:field="catalog_version"
> sql:datatype="nvarchar(50)" />
> <!-- CatalogDelta -->
> <xsd:group name="DeltaCatalogGroup">
> <xsd:sequence>
> <xsd:element ref="RevisionID"/>
> <xsd:element ref="CatalogVersion" />
> </xsd:sequence>
> </xsd:group>
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
> <xsd:group ref="DeltaCatalogGroup"/>
> </xsd:complexType>
> </xsd:element>
> <!-- Deletes -->
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <!-- Delete Albums -->
> <xsd:element name="album" sql:relation="album_deletes">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
> <!-- Delete Songs -->
> <xsd:element name="song" sql:relation="song_deletes">
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string"
> sql:field="song_id" sql:datatype="nvarchar(50)" />
> </xsd:complexType>
> </xsd:element>
> </xsd:all>
> </xsd:complexType>
> </xsd:element>
> </schema>
> When I bulk load using this schema, the contents get put into the
> CatalogDelta table but nothing gets put into the album_deletes or
> song_deletes tables.
> Can someone point out what I am doing wrong, please?
> Thank you for your help.
> Sincerely
> Steve Cummings
>|||On Apr 10, 2:54 pm, Monica Frintu [MSFT]
<MonicaFrintuM...@.discussions.microsoft.com> wrote:
> Hello,
> Whenever you have children you have todefinerelationship in theschema:
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="catalog_album"
> parent="CatalogDelta"
> child="album_deletes"
> parent-key="?"
> child-key="?"/>
> </xsd:appinfo>
> </xsd:annotation>
> Then add the deletes element to the Catalog:
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:group ref="DeltaCatalogGroup"/>
> <xsd:element ref="deletes" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <xsd:element name="album" sql:relation="album_deletes"
> sql:relationship="catalog_album">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
> You need to figure out how the tables related to each other and describe
> this in theschema.
> Take a look at
> this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
> I hope this helps.
> Regards,
> Monica Frintu
>
> "sgcummi...@.sbcglobal.net" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hello Monica!
Thank you for the information. It was very helpful. I was able to
extrapolate from what you provided and have everything working now.
It only took about 20 minutes to complete all the adjustments to get
the schema bulk load to work.
With much appreciation for your help.
Steve Cummings

How to define the periodicity_Hint value

Hi, all experts here,

I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?

Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The preiodicity hint should be specified as a string that looks as follows:

{a,b,c}

where a,b and c are the hints you specify. In your example, if you have a periodicity in the data of 12, the hint string would look like {12}.

|||

Hi, Shuvro,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

But my another question on it is: assume we did not know the periodicity of the data, so it is always better to leave this propertity blank in which case the system willl automatically detect the periodicity of the data?

Thank you very much and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||Exactly: the system tries to detect the periodicity of the data.
If you leave this blank, the results of the detection are used.

You should provide a value for this parameter when:
- you know the periodicity (and want to hint the system, before it runs the detection step) OR
- you know the periodicity and want to correct the algorithm's auto-detection results

In both cases, your periodicity hints will be analyzed together with the periods automatically detected by the algorithm|||

Hi, all friends,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

How to define the periodicity_Hint value

Hi, all experts here,

I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?

Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The preiodicity hint should be specified as a string that looks as follows:

{a,b,c}

where a,b and c are the hints you specify. In your example, if you have a periodicity in the data of 12, the hint string would look like {12}.

|||

Hi, Shuvro,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

But my another question on it is: assume we did not know the periodicity of the data, so it is always better to leave this propertity blank in which case the system willl automatically detect the periodicity of the data?

Thank you very much and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||Exactly: the system tries to detect the periodicity of the data.
If you leave this blank, the results of the detection are used.

You should provide a value for this parameter when:
- you know the periodicity (and want to hint the system, before it runs the detection step) OR
- you know the periodicity and want to correct the algorithm's auto-detection results

In both cases, your periodicity hints will be analyzed together with the periods automatically detected by the algorithm|||

Hi, all friends,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?
On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows?
|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:

>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:

>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?

How to define programatically the width for columns?

Hi everyone,

Either Sql2k or Sql25k are targeted if you answer to this thread. When we have source/destination files we usually wish to define its properties, the width for each field and so on. My question is related with this, how do such by-hand tasks via scripting inside the own ETL? Tedious tasks are if there are more than 20 columns.

Is it possible? I think so regarding 2005 but about 2000 I haven't idea at all how to begin. Issue comes when one programmer must alter lots of columns due to for example, a new file format from mainframe is released.

Thanks in advance for your time or advices,

Any ideas?

How to define multi-valued 'default value' parameters for a report on the ReportServer

Can someone please explain how i would define a multi-valued default parameter within the report Properties -> Parameters. I have an OLAP based report with multi-value parameters. I do not want to set the default values from within BIDS. Instead, I'd like to do this from the ReportServer (after report deployment). I have no problem when i enter a single value as a 'default value', for example:

ReportParm1 String [Deal Dim].[Shelf].&[AAM]

But, how would i define it with multiple values as a 'default value' ?, for example:

ReportParm1 String [Deal Dim].[Shelf].&[ABC] , [Deal Dim].[Shelf].&[DEF]

NOTE: It appears that you cannot use expressions, such as the 'split' function in the 'default value' space.

Any help would be greatly appreciated.

thank you.

Once you publish the report, you cannot edit the expressions that define the defaut or valid values. If you have a set of expressions in the RDL, you'll be able to choose from the evaluated values of the expressions, but you won't be able to modify the expressions themselves.

If you just want to choose from a list of values derived from expressions, put the expressions in the RDL and mark the parameter as MultiValue:

<ReportParameter Name="IntegerParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=(1+1)</Value>
<Value>123456</Value>
<Value>-123456</Value>
<Value>=CInt(123456/123456)</Value>
</Values>
</DefaultValue>
<MultiValue>true</MultiValue>
<Prompt>Integer</Prompt>
</ReportParameter>

If you're meaning to emit the strings representing the expressions instead of the evaluated expressions, then just make the parameter a multivalued string parameter:

<ReportParameter Name="IntegerParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>="(1+1)"</Value>
<Value>="123456"</Value>
<Value>="-123456"</Value>
<Value>="CInt(123456/123456)"</Value>
</Values>
</DefaultValue>
<MultiValue>true</MultiValue>
<Prompt>Integer</Prompt>
</ReportParameter>

Now you can use the string value as an expression.

How to define multi-valued and composite attribute

Hello All,
I want to implement an address field in a table. But,
this address field is a multi-valued attribute. Also, I want to make it
as a composite field(so that all the info viz, city,st,zip,tel,email
etc can be tied together)
Can anyone tell me how to implement this using T-SQL?I sounds as though you want to store the entire address in a single
table column. You COULD just use a long string, say varchar(255) for
example, but do that and you will never be able to query any of the
individual parts without driving yourself mad in the process. Do you
really want to give up being able to ask for just the addresses in
California? Or just the phone numbers in a specific area code?
The proper way to do this in a database is with multiple columns. One
(two is better) for street address, one for city, one for state, one
for zip, one for phone, one for email. If you need to string them
together that is easy to do when you retrieve them.
Roy Harvey
Beacon Falls, CT
On 3 Mar 2006 10:45:35 -0800, "juventus" <saurabh.kotkar@.gmail.com>
wrote:

>Hello All,
> I want to implement an address field in a table. But,
>this address field is a multi-valued attribute. Also, I want to make it
>as a composite field(so that all the info viz, city,st,zip,tel,email
>etc can be tied together)
>Can anyone tell me how to implement this using T-SQL?

How to define KPI dynamically?

Hi, all here,

Thanks for your kind attention.

I wonder is there any way for us to define a KPI dynamically? What I mean is whenever we browse the cube across different levels with a KPI, we can always see the KPI bound to different levels? E.g. when I browse sales for a specific region in a particular year, I can browse its KPI, and then when I change the level to a particular country instead of region level, still I am able to see its KPI.

Hope my question is clear.

I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

Hi , dear experts here,

Any advices? Thank you.

With best regards,

Yours sincerely,

|||

This is how KPI's work any way - If you define the KPI components based off values or calculations in your cubes.

If you defined a KPI as measures.[Gross Sales] you would see a different value for a city as opposed to a region or country.

|||

Hi, Darren,

Thank you for your advices.

Could you kindly post an example piece of code here for the above KPI? E.g. [Gross Sale] across different levels of region or country. Thank you.

With best regards,

Yours sincerely,

How to define KPI dynamically?

Hi, all here,

Thanks for your kind attention.

I wonder is there any way for us to define a KPI dynamically? What I mean is whenever we browse the cube across different levels with a KPI, we can always see the KPI bound to different levels? E.g. when I browse sales for a specific region in a particular year, I can browse its KPI, and then when I change the level to a particular country instead of region level, still I am able to see its KPI.

Hope my question is clear.

I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

Hi , dear experts here,

Any advices? Thank you.

With best regards,

Yours sincerely,

|||

This is how KPI's work any way - If you define the KPI components based off values or calculations in your cubes.

If you defined a KPI as measures.[Gross Sales] you would see a different value for a city as opposed to a region or country.

|||

Hi, Darren,

Thank you for your advices.

Could you kindly post an example piece of code here for the above KPI? E.g. [Gross Sale] across different levels of region or country. Thank you.

With best regards,

Yours sincerely,

How to define Hierarchy

Hi all,

I have a Store dimension that has 4 attributes.

Store

Category

Department

Store Key (Key column)

|_Store, Category, Department

Then I have creaet one Hierachy as follow:

Store->Category->Department

But I get a warning message "Attribute relationship doesn't exist in one or more level". Do I need to worry about it?

I know how to solve this warning message by creating a member property of the attribute for a level on the level below, but the problem is a Department can be in different Category in different Store. If I apply this solution, then the hierachy can't display correctly

Thanks

Hello! If you check the attribute relation in a natural or user hierarchy they can only have one-to-one or one-to-many relations.

So a child in a natural hierarchy cannot point to more than parent. If they point to several you will have random results when you analyze data. I think that SSAS2005 will pick the first parent it finds and ignore the others.

In your natural hierarchy department can only point to one parent(category).

HTH

Thomas Ivarsson

|||

Take a look at this site, and see if it helps you..

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262358,00.html

|||

Thanks, this site is really helpfull. I think I can define the un-natural hierarchies on my Store dimension, but I need to suffer the query performance.