Hi,
Each time, I execute a job, I keep getting the above
error. Please Help!Check the following articles:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
-Sue
On Fri, 10 Oct 2003 03:27:36 -0700, "Yinks"
<olayimika74@.hotmail.com> wrote:
>Hi,
>Each time, I execute a job, I keep getting the above
>error. Please Help!
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Monday, March 26, 2012
Many-to-Many relationship with Time dimension?
Is it possible to use a Time dimension as the intermediate dimension in a many-to-many relationship? So far, I have not been able to make it work. When I attempt to setup the relationship, I get a warning on the Define Relationship window that says:
This relationship cannot be defined because no intermediate measure group or dimension table exists.
However, the intermediate measure group and dimension tables do exist. The New Cube... wizard picks up the tables but creates all the relationships as Regular, in effect I have two separate measure groups. Do I have to do something special to define a measure group as intermediate?
The business problem I'm trying to model here is special time periods that can overlap - think college and university semesters. I can have a semester where any given day (the granularity of my time dimension) can exist in multiple semesters. So I am trying to create a many-to-many relationship from my "semesters" to an intermediate mesaure group to time dimension to real measure group.
Any help or guidance is much appreciated!
Hello. Check for intermediate dimensions in BooksOnLine. This is what I think you are trying do do. Many-to-many dimensions are between a normal fact table and a many-to-many artificial fact table with the many-to-many relation with a dimension table.
A good example is an account that is shared between two persons. You have the single account in the dimension and the many-to-many relation between the persons and the account in the artificial fact table.
Here is a link to a Blog about the new dimension types in SSAS2005: http://blogs.conchango.com/christianwade/archive/2005/04/07/1255.aspx
An intermediate dimension is when you do not have a direct relation between a fact table and a dimension. Normally you have a direct relation.
The use for this type is more limited since that you can always enter more dimenion keys into a fact table.
I would think about two new attributes in the time dimension like:
-CollegeSemesterDayFlag
-UniversitySemesterDayFlag
HTH
Thomas Ivarsson
|||Thanks for the response Thomas. I do want a many-to-many dimension, I just didn't explain my business problem clearly. I have potentially multiple, overlapping, arbitrary time spans (again, think college/university semesters), where any given day can exist in multiple semesters. This is very similar to the example you gave above with the accounts shared between multiple people. My problem is that I could have a semester called FALL from Sep1-Dec31 and a semester called FALL_SPECIAL Oct1-Oct31, where the date range Oct1-Oct31 falls into two semesters. Also, from year to year, there will be different semesters - some years won't have a FALL_SPECIAL, but have a WINTER_SPECIAL, etc.
Anyway, I solved my problem and it was because the New Cube... wizard didn't find all the relationships. I am using my time dimension as the intermediate dimension and it is also acting as a role playing dimension. The New Cube... wizard didn't pick up all those relationships, so I had to manually setup to the many-to-many relationship for all five of my role playing time dimensions, only then it recognized the second fact table as an intermediate fact table. I also had to delete a time dimension that showed up in the Dimension Usage tab that didn't belong but the New Cube... wizard put in there.
I haven't explored all the data to make sure it's aggregating correctly, but it appears to be doing what I want and I'm quite impressed I might add! I just need to verify that the ParallelPeriod() and Cousin() functions behave properly.
|||Great that you have solved the problem.
I think that this is no more than standard many-to-many modelling.
You have several colleges and universities on top with their own semester schedule and this means that it will be a many-to many relation with the time dimension.
This means an education institution-semester dimension on top of the time dimension.
Regards
Thomas
sqlWednesday, March 21, 2012
many inserts results in a massive reserved space for table
Hi,
I've got a real problem inserting approx 1,000,000 rows of data into
some SQL server 2000 tables. The data is being inserted 1 row at a
time using a T-sql cursor. When I view the table size using
sp_spaceused I get the following type of results:
reserved = 27021280 KB
data = 3376216 KB
Unused = 23642952
why is the reserved space sooooo much more than the actual amount of
data in the table?
The table cannot be fragmented as this is the first data which has
been inserted into it.
I cannot use a different insert method (like DTS) as logic needs to be
applied to the data before it is inserted.
Is there any way round this?
Shrinking the tables after the event is also not an option, as I would
run out of disk space way before all of the tables are populated.Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
Also, what indexes do you have on the table. This along with the data distribution of the data you
are inserting will determine the level of fragmentation you get when you perform your inserts. Did
you check fragmentation level using DBCC SHOWCONTIG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jackie" <jackiesmith_3@.hotmail.com> wrote in message
news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
> Hi,
> I've got a real problem inserting approx 1,000,000 rows of data into
> some SQL server 2000 tables. The data is being inserted 1 row at a
> time using a T-sql cursor. When I view the table size using
> sp_spaceused I get the following type of results:
> reserved = 27021280 KB
> data = 3376216 KB
> Unused = 23642952
> why is the reserved space sooooo much more than the actual amount of
> data in the table?
> The table cannot be fragmented as this is the first data which has
> been inserted into it.
> I cannot use a different insert method (like DTS) as logic needs to be
> applied to the data before it is inserted.
> Is there any way round this?
> Shrinking the tables after the event is also not an option, as I would
> run out of disk space way before all of the tables are populated.|||Thanks for your advice, I am new to SQL server so had not heard of
these procedures before...
DBCC UPDATEUSAGE - has no effect.
When I do DBCC SHOWCONTIG it shows that the table is MASSIVELY
fragmented (at least I think that's what it's saying) - results:
- Pages Scanned........................: 429774
- Extents Scanned.......................: 429755
- Extent Switches.......................: 429754
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [53722:429755]
- Extent Scan Fragmentation ...............: 98.13%
- Avg. Bytes Free per Page................: 7712.6
- Avg. Page Density (full)................: 4.71%
There are no indexes on the table at all (I am trying this routine on
a test database before running it elsewhere and I assumed that leaving
off the indexes would increase the rate of the inserts) - are you
suggesting that if I had the indexes on the table it would reduce the
fragmentation as the data is inserted?
you say "This along with the data distribution of the data you are
inserting", but how can I control where the data is physically written
to? (and therefore control the fragmentation)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<uRyrH1iqEHA.1992@.TK2MSFTNGP09.phx.gbl>...
> Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
> Also, what indexes do you have on the table. This along with the data distribution of the data you
> are inserting will determine the level of fragmentation you get when you perform your inserts. Did
> you check fragmentation level using DBCC SHOWCONTIG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jackie" <jackiesmith_3@.hotmail.com> wrote in message
> news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
> > Hi,
> >
> > I've got a real problem inserting approx 1,000,000 rows of data into
> > some SQL server 2000 tables. The data is being inserted 1 row at a
> > time using a T-sql cursor. When I view the table size using
> > sp_spaceused I get the following type of results:
> >
> > reserved = 27021280 KB
> > data = 3376216 KB
> > Unused = 23642952
> >
> > why is the reserved space sooooo much more than the actual amount of
> > data in the table?
> > The table cannot be fragmented as this is the first data which has
> > been inserted into it.
> >
> > I cannot use a different insert method (like DTS) as logic needs to be
> > applied to the data before it is inserted.
> >
> > Is there any way round this?
> >
> > Shrinking the tables after the event is also not an option, as I would
> > run out of disk space way before all of the tables are populated.|||The pages seems indeed very empty, on average. To say anything more conclusive, we would need the
table layout and what indexes you have on the table. You say no indexes, but that means they you
didn't define a primary key (or unique constraint). This is not recommended! Sp_helpindex will list
the indexes you have on the table.
In general every table should have a clustered index. Which column(s) you define in the clustered
index is based on both the data distribution and your queries. It is likely that you will have less
"emptiness" in the pages with a proper clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jackie" <jackiesmith_3@.hotmail.com> wrote in message
news:6cb5ab33.0410050059.7bbdb78f@.posting.google.com...
> Thanks for your advice, I am new to SQL server so had not heard of
> these procedures before...
> DBCC UPDATEUSAGE - has no effect.
> When I do DBCC SHOWCONTIG it shows that the table is MASSIVELY
> fragmented (at least I think that's what it's saying) - results:
> - Pages Scanned........................: 429774
> - Extents Scanned.......................: 429755
> - Extent Switches.......................: 429754
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 12.50% [53722:429755]
> - Extent Scan Fragmentation ...............: 98.13%
> - Avg. Bytes Free per Page................: 7712.6
> - Avg. Page Density (full)................: 4.71%
> There are no indexes on the table at all (I am trying this routine on
> a test database before running it elsewhere and I assumed that leaving
> off the indexes would increase the rate of the inserts) - are you
> suggesting that if I had the indexes on the table it would reduce the
> fragmentation as the data is inserted?
> you say "This along with the data distribution of the data you are
> inserting", but how can I control where the data is physically written
> to? (and therefore control the fragmentation)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<uRyrH1iqEHA.1992@.TK2MSFTNGP09.phx.gbl>...
>> Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
>> Also, what indexes do you have on the table. This along with the data distribution of the data
>> you
>> are inserting will determine the level of fragmentation you get when you perform your inserts.
>> Did
>> you check fragmentation level using DBCC SHOWCONTIG?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jackie" <jackiesmith_3@.hotmail.com> wrote in message
>> news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
>> > Hi,
>> >
>> > I've got a real problem inserting approx 1,000,000 rows of data into
>> > some SQL server 2000 tables. The data is being inserted 1 row at a
>> > time using a T-sql cursor. When I view the table size using
>> > sp_spaceused I get the following type of results:
>> >
>> > reserved = 27021280 KB
>> > data = 3376216 KB
>> > Unused = 23642952
>> >
>> > why is the reserved space sooooo much more than the actual amount of
>> > data in the table?
>> > The table cannot be fragmented as this is the first data which has
>> > been inserted into it.
>> >
>> > I cannot use a different insert method (like DTS) as logic needs to be
>> > applied to the data before it is inserted.
>> >
>> > Is there any way round this?
>> >
>> > Shrinking the tables after the event is also not an option, as I would
>> > run out of disk space way before all of the tables are populated.|||Well, I don't understand at all!!
It was true that the tables did not have indexes or primary keys on -
this was simply because I was running a test and I thought it would
run more quickly - again my not understanding SQL Server properly (not
because I think it's a good idea in terms of design!).
After your latest message - here's what I did (sorry if this is
longwinded).
I loaded the indexes / PKs onto the relevent tables - although I
notice there are no clustered indexes on any of the 5 tables which I
am inserting data into - please bear in mind though that I have no
influence over the design of the database...
I re-ran my routine against THE SAME SET OF DATA - this time the data
loaded in a 100th of the time, and took up approx 350Mb per table!!
(prior to this the tables had 27Gb of reserved space, with maybe 3.5Gb
of data) - HOW CAN THIS BE SO DIFFERENT? (total size of database was
61Gb and now is 3Gb !!)
So, then I tried to run my same routine loading data into a different
database on a different server which already had the indexes on (same
data model). This server only had 12Gb of free space (the total data
in my first db took up 1.9Gb). Afetr a very short space of time the
database ran out of disk space after only a fraction of the data had
been inserted. Looking at SP_SPACEUSED, the reserved space was way out
of sync with the data figure as before.
I ran DBCC UPDATEUSAGE, truncated the 5 tables and re-ran my routine,
now the data is inserting happily and is taking up 353Mb per table
again!
I have no more servers to play with!!
Is the data "behaving properly" the 2nd time around simply because it
is the 2nd time the same routine has been run?
Or is the fact that each time I have run DBCC UPDATEUSAGE relevent?
Surely there must be a way of achieving the proper data figures during
the first time this routine is run (I clearly can't keep running the
same routines and running out of disk space when I come to run this
against production databases).
For reference - here is an example of one of the tables I am inserting
data into (results from sp_help) -
BookingPayment dbo user table 2004-08-24 11:48:44.210
BooRefNo int no 4 10 0 no (n/a) (n/a) NULL
PayID int no 4 10 0 no (n/a) (n/a) NULL
BpyDate datetime no 8 no (n/a) (n/a) NULL
BpyAmount money no 8 19 4 no (n/a) (n/a) NULL
BpyTzoName varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyDateUTC datetime no 8 yes (n/a) (n/a) NULL
BpyPayAmount money no 8 19 4 yes (n/a) (n/a) NULL
BpyExrRate float no 8 53 NULL yes (n/a) (n/a) NULL
WrkID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
UseID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedWkgID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedUgrID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedProID varchar no 5
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedPrgID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyInvStatus tinyint no 1 3 0 yes (n/a) (n/a) NULL
PaymentKey nonclustered, unique located on PRIMARY PayID, BooRefNo
PK___3__21 nonclustered, unique, primary key located on
PRIMARY BooRefNo, PayID, BpyDate
PRIMARY KEY (non-clustered) PK___3__21 (n/a) (n/a) (n/a) (n/a) BooRefNo,
PayID, BpyDate
there is a difference in 27Gb (!!!) in total space used between the
1st and 2nd times I insert data into this table...
I've got a real problem inserting approx 1,000,000 rows of data into
some SQL server 2000 tables. The data is being inserted 1 row at a
time using a T-sql cursor. When I view the table size using
sp_spaceused I get the following type of results:
reserved = 27021280 KB
data = 3376216 KB
Unused = 23642952
why is the reserved space sooooo much more than the actual amount of
data in the table?
The table cannot be fragmented as this is the first data which has
been inserted into it.
I cannot use a different insert method (like DTS) as logic needs to be
applied to the data before it is inserted.
Is there any way round this?
Shrinking the tables after the event is also not an option, as I would
run out of disk space way before all of the tables are populated.Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
Also, what indexes do you have on the table. This along with the data distribution of the data you
are inserting will determine the level of fragmentation you get when you perform your inserts. Did
you check fragmentation level using DBCC SHOWCONTIG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jackie" <jackiesmith_3@.hotmail.com> wrote in message
news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
> Hi,
> I've got a real problem inserting approx 1,000,000 rows of data into
> some SQL server 2000 tables. The data is being inserted 1 row at a
> time using a T-sql cursor. When I view the table size using
> sp_spaceused I get the following type of results:
> reserved = 27021280 KB
> data = 3376216 KB
> Unused = 23642952
> why is the reserved space sooooo much more than the actual amount of
> data in the table?
> The table cannot be fragmented as this is the first data which has
> been inserted into it.
> I cannot use a different insert method (like DTS) as logic needs to be
> applied to the data before it is inserted.
> Is there any way round this?
> Shrinking the tables after the event is also not an option, as I would
> run out of disk space way before all of the tables are populated.|||Thanks for your advice, I am new to SQL server so had not heard of
these procedures before...
DBCC UPDATEUSAGE - has no effect.
When I do DBCC SHOWCONTIG it shows that the table is MASSIVELY
fragmented (at least I think that's what it's saying) - results:
- Pages Scanned........................: 429774
- Extents Scanned.......................: 429755
- Extent Switches.......................: 429754
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [53722:429755]
- Extent Scan Fragmentation ...............: 98.13%
- Avg. Bytes Free per Page................: 7712.6
- Avg. Page Density (full)................: 4.71%
There are no indexes on the table at all (I am trying this routine on
a test database before running it elsewhere and I assumed that leaving
off the indexes would increase the rate of the inserts) - are you
suggesting that if I had the indexes on the table it would reduce the
fragmentation as the data is inserted?
you say "This along with the data distribution of the data you are
inserting", but how can I control where the data is physically written
to? (and therefore control the fragmentation)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<uRyrH1iqEHA.1992@.TK2MSFTNGP09.phx.gbl>...
> Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
> Also, what indexes do you have on the table. This along with the data distribution of the data you
> are inserting will determine the level of fragmentation you get when you perform your inserts. Did
> you check fragmentation level using DBCC SHOWCONTIG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jackie" <jackiesmith_3@.hotmail.com> wrote in message
> news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
> > Hi,
> >
> > I've got a real problem inserting approx 1,000,000 rows of data into
> > some SQL server 2000 tables. The data is being inserted 1 row at a
> > time using a T-sql cursor. When I view the table size using
> > sp_spaceused I get the following type of results:
> >
> > reserved = 27021280 KB
> > data = 3376216 KB
> > Unused = 23642952
> >
> > why is the reserved space sooooo much more than the actual amount of
> > data in the table?
> > The table cannot be fragmented as this is the first data which has
> > been inserted into it.
> >
> > I cannot use a different insert method (like DTS) as logic needs to be
> > applied to the data before it is inserted.
> >
> > Is there any way round this?
> >
> > Shrinking the tables after the event is also not an option, as I would
> > run out of disk space way before all of the tables are populated.|||The pages seems indeed very empty, on average. To say anything more conclusive, we would need the
table layout and what indexes you have on the table. You say no indexes, but that means they you
didn't define a primary key (or unique constraint). This is not recommended! Sp_helpindex will list
the indexes you have on the table.
In general every table should have a clustered index. Which column(s) you define in the clustered
index is based on both the data distribution and your queries. It is likely that you will have less
"emptiness" in the pages with a proper clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jackie" <jackiesmith_3@.hotmail.com> wrote in message
news:6cb5ab33.0410050059.7bbdb78f@.posting.google.com...
> Thanks for your advice, I am new to SQL server so had not heard of
> these procedures before...
> DBCC UPDATEUSAGE - has no effect.
> When I do DBCC SHOWCONTIG it shows that the table is MASSIVELY
> fragmented (at least I think that's what it's saying) - results:
> - Pages Scanned........................: 429774
> - Extents Scanned.......................: 429755
> - Extent Switches.......................: 429754
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 12.50% [53722:429755]
> - Extent Scan Fragmentation ...............: 98.13%
> - Avg. Bytes Free per Page................: 7712.6
> - Avg. Page Density (full)................: 4.71%
> There are no indexes on the table at all (I am trying this routine on
> a test database before running it elsewhere and I assumed that leaving
> off the indexes would increase the rate of the inserts) - are you
> suggesting that if I had the indexes on the table it would reduce the
> fragmentation as the data is inserted?
> you say "This along with the data distribution of the data you are
> inserting", but how can I control where the data is physically written
> to? (and therefore control the fragmentation)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<uRyrH1iqEHA.1992@.TK2MSFTNGP09.phx.gbl>...
>> Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
>> Also, what indexes do you have on the table. This along with the data distribution of the data
>> you
>> are inserting will determine the level of fragmentation you get when you perform your inserts.
>> Did
>> you check fragmentation level using DBCC SHOWCONTIG?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jackie" <jackiesmith_3@.hotmail.com> wrote in message
>> news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
>> > Hi,
>> >
>> > I've got a real problem inserting approx 1,000,000 rows of data into
>> > some SQL server 2000 tables. The data is being inserted 1 row at a
>> > time using a T-sql cursor. When I view the table size using
>> > sp_spaceused I get the following type of results:
>> >
>> > reserved = 27021280 KB
>> > data = 3376216 KB
>> > Unused = 23642952
>> >
>> > why is the reserved space sooooo much more than the actual amount of
>> > data in the table?
>> > The table cannot be fragmented as this is the first data which has
>> > been inserted into it.
>> >
>> > I cannot use a different insert method (like DTS) as logic needs to be
>> > applied to the data before it is inserted.
>> >
>> > Is there any way round this?
>> >
>> > Shrinking the tables after the event is also not an option, as I would
>> > run out of disk space way before all of the tables are populated.|||Well, I don't understand at all!!
It was true that the tables did not have indexes or primary keys on -
this was simply because I was running a test and I thought it would
run more quickly - again my not understanding SQL Server properly (not
because I think it's a good idea in terms of design!).
After your latest message - here's what I did (sorry if this is
longwinded).
I loaded the indexes / PKs onto the relevent tables - although I
notice there are no clustered indexes on any of the 5 tables which I
am inserting data into - please bear in mind though that I have no
influence over the design of the database...
I re-ran my routine against THE SAME SET OF DATA - this time the data
loaded in a 100th of the time, and took up approx 350Mb per table!!
(prior to this the tables had 27Gb of reserved space, with maybe 3.5Gb
of data) - HOW CAN THIS BE SO DIFFERENT? (total size of database was
61Gb and now is 3Gb !!)
So, then I tried to run my same routine loading data into a different
database on a different server which already had the indexes on (same
data model). This server only had 12Gb of free space (the total data
in my first db took up 1.9Gb). Afetr a very short space of time the
database ran out of disk space after only a fraction of the data had
been inserted. Looking at SP_SPACEUSED, the reserved space was way out
of sync with the data figure as before.
I ran DBCC UPDATEUSAGE, truncated the 5 tables and re-ran my routine,
now the data is inserting happily and is taking up 353Mb per table
again!
I have no more servers to play with!!
Is the data "behaving properly" the 2nd time around simply because it
is the 2nd time the same routine has been run?
Or is the fact that each time I have run DBCC UPDATEUSAGE relevent?
Surely there must be a way of achieving the proper data figures during
the first time this routine is run (I clearly can't keep running the
same routines and running out of disk space when I come to run this
against production databases).
For reference - here is an example of one of the tables I am inserting
data into (results from sp_help) -
BookingPayment dbo user table 2004-08-24 11:48:44.210
BooRefNo int no 4 10 0 no (n/a) (n/a) NULL
PayID int no 4 10 0 no (n/a) (n/a) NULL
BpyDate datetime no 8 no (n/a) (n/a) NULL
BpyAmount money no 8 19 4 no (n/a) (n/a) NULL
BpyTzoName varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyDateUTC datetime no 8 yes (n/a) (n/a) NULL
BpyPayAmount money no 8 19 4 yes (n/a) (n/a) NULL
BpyExrRate float no 8 53 NULL yes (n/a) (n/a) NULL
WrkID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
UseID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedWkgID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedUgrID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedProID varchar no 5
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedPrgID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyInvStatus tinyint no 1 3 0 yes (n/a) (n/a) NULL
PaymentKey nonclustered, unique located on PRIMARY PayID, BooRefNo
PK___3__21 nonclustered, unique, primary key located on
PRIMARY BooRefNo, PayID, BpyDate
PRIMARY KEY (non-clustered) PK___3__21 (n/a) (n/a) (n/a) (n/a) BooRefNo,
PayID, BpyDate
there is a difference in 27Gb (!!!) in total space used between the
1st and 2nd times I insert data into this table...
manualy
Hi
In replication shedule properties i gave the option continuesly,i think what
ever modifications we made same time it will replicate.with out setting any
time period how can i do the replication manualy.
Thanks
In the distribution (or merge) agent's job schedule change the option from
'Start automatically when SQL Server agent starts' to 'Recurring' which'll
allow you to specify a schedule.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
In replication shedule properties i gave the option continuesly,i think what
ever modifications we made same time it will replicate.with out setting any
time period how can i do the replication manualy.
Thanks
In the distribution (or merge) agent's job schedule change the option from
'Start automatically when SQL Server agent starts' to 'Recurring' which'll
allow you to specify a schedule.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Monday, March 19, 2012
Manually Triggering Subcriptions
Is there a way to do this? It would be useful to test whether a subsciption works. At the moment I am setting the scheduled time a few minutes ahead and waiting for the report to run.
Subsciptions are also a convenient way to store different parameter settings for a report.
My preference would be to to do this through the Report Server UI (presumably from the subscriptions tab).
Alernatively, being able to call a report (with paramters) from a stored procedure woukd be useful.
Thanks,
Dick Campbell
Yes, but not through the UI as far as I know.
Look at this http://msdn2.microsoft.com/it-it/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx
|||Thanks. I guess I could write a CLR stored procedure with the server name and the subcription name as parameters. That should make it relatively easy for users to manually trigger a subsciption.
It would be nice to be able to trigger subscriptions from the UI. As I have said, subsciptions provide a way to store different parameter settings for later execution something that many of our customers would value.
Manually Triggering Subcriptions
Is there a way to do this? It would be useful to test whether a subsciption works. At the moment I am setting the scheduled time a few minutes ahead and waiting for the report to run.
Subsciptions are also a convenient way to store different parameter settings for a report.
My preference would be to to do this through the Report Server UI (presumably from the subscriptions tab).
Alernatively, being able to call a report (with paramters) from a stored procedure woukd be useful.
Thanks,
Dick Campbell
Yes, but not through the UI as far as I know.
Look at this http://msdn2.microsoft.com/it-it/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx
|||Thanks. I guess I could write a CLR stored procedure with the server name and the subcription name as parameters. That should make it relatively easy for users to manually trigger a subsciption.
It would be nice to be able to trigger subscriptions from the UI. As I have said, subsciptions provide a way to store different parameter settings for later execution something that many of our customers would value.
Manual Starting SQL Server ... delay...
When I start the SQL Server by command prompt for single user mode it is
taking lots of time(15 minutes) to start the SQL Server. My computer is a
slow system but it is not very bad slow computer.
IS it a resonable time or not?
SQL 2K.
Thanks,
Smith
Therefore you should look in the SQLServer logs,how much time the databases
need to come up,on every start the database are checked while opened if any
inconsictencys exsists. If so,the database is "repaired" (open transactions
are rolled back,etc.). The betteryou leave the system duringthe shutdown the
faster it gets up on a restart.
SQL Server logs can be found in the Control node of SQL Server -- Current
Log.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Arora Smith" <Arora_Smith@.hotmail.com> schrieb im Newsbeitrag
news:%233MaZErfFHA.460@.TK2MSFTNGP09.phx.gbl...
> When I start the SQL Server by command prompt for single user mode it is
> taking lots of time(15 minutes) to start the SQL Server. My computer is a
> slow system but it is not very bad slow computer.
> IS it a resonable time or not?
> SQL 2K.
> Thanks,
> Smith
>
>
taking lots of time(15 minutes) to start the SQL Server. My computer is a
slow system but it is not very bad slow computer.
IS it a resonable time or not?
SQL 2K.
Thanks,
Smith
Therefore you should look in the SQLServer logs,how much time the databases
need to come up,on every start the database are checked while opened if any
inconsictencys exsists. If so,the database is "repaired" (open transactions
are rolled back,etc.). The betteryou leave the system duringthe shutdown the
faster it gets up on a restart.
SQL Server logs can be found in the Control node of SQL Server -- Current
Log.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Arora Smith" <Arora_Smith@.hotmail.com> schrieb im Newsbeitrag
news:%233MaZErfFHA.460@.TK2MSFTNGP09.phx.gbl...
> When I start the SQL Server by command prompt for single user mode it is
> taking lots of time(15 minutes) to start the SQL Server. My computer is a
> slow system but it is not very bad slow computer.
> IS it a resonable time or not?
> SQL 2K.
> Thanks,
> Smith
>
>
Manual Starting SQL Server ... delay...
When I start the SQL Server by command prompt for single user mode it is
taking lots of time(15 minutes) to start the SQL Server. My computer is a
slow system but it is not very bad slow computer.
IS it a resonable time or not?
SQL 2K.
Thanks,
SmithTherefore you should look in the SQLServer logs,how much time the databases
need to come up,on every start the database are checked while opened if any
inconsictencys exsists. If so,the database is "repaired" (open transactions
are rolled back,etc.). The betteryou leave the system duringthe shutdown the
faster it gets up on a restart.
SQL Server logs can be found in the Control node of SQL Server -- Current
Log.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Arora Smith" <Arora_Smith@.hotmail.com> schrieb im Newsbeitrag
news:%233MaZErfFHA.460@.TK2MSFTNGP09.phx.gbl...
> When I start the SQL Server by command prompt for single user mode it is
> taking lots of time(15 minutes) to start the SQL Server. My computer is a
> slow system but it is not very bad slow computer.
> IS it a resonable time or not?
> SQL 2K.
> Thanks,
> Smith
>
>
taking lots of time(15 minutes) to start the SQL Server. My computer is a
slow system but it is not very bad slow computer.
IS it a resonable time or not?
SQL 2K.
Thanks,
SmithTherefore you should look in the SQLServer logs,how much time the databases
need to come up,on every start the database are checked while opened if any
inconsictencys exsists. If so,the database is "repaired" (open transactions
are rolled back,etc.). The betteryou leave the system duringthe shutdown the
faster it gets up on a restart.
SQL Server logs can be found in the Control node of SQL Server -- Current
Log.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Arora Smith" <Arora_Smith@.hotmail.com> schrieb im Newsbeitrag
news:%233MaZErfFHA.460@.TK2MSFTNGP09.phx.gbl...
> When I start the SQL Server by command prompt for single user mode it is
> taking lots of time(15 minutes) to start the SQL Server. My computer is a
> slow system but it is not very bad slow computer.
> IS it a resonable time or not?
> SQL 2K.
> Thanks,
> Smith
>
>
Manual Starting SQL Server ... delay...
When I start the SQL Server by command prompt for single user mode it is
taking lots of time(15 minutes) to start the SQL Server. My computer is a
slow system but it is not very bad slow computer.
IS it a resonable time or not?
SQL 2K.
Thanks,
SmithTherefore you should look in the SQLServer logs,how much time the databases
need to come up,on every start the database are checked while opened if any
inconsictencys exsists. If so,the database is "repaired" (open transactions
are rolled back,etc.). The betteryou leave the system duringthe shutdown the
faster it gets up on a restart.
SQL Server logs can be found in the Control node of SQL Server -- Current
Log.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Arora Smith" <Arora_Smith@.hotmail.com> schrieb im Newsbeitrag
news:%233MaZErfFHA.460@.TK2MSFTNGP09.phx.gbl...
> When I start the SQL Server by command prompt for single user mode it is
> taking lots of time(15 minutes) to start the SQL Server. My computer is a
> slow system but it is not very bad slow computer.
> IS it a resonable time or not?
> SQL 2K.
> Thanks,
> Smith
>
>
taking lots of time(15 minutes) to start the SQL Server. My computer is a
slow system but it is not very bad slow computer.
IS it a resonable time or not?
SQL 2K.
Thanks,
SmithTherefore you should look in the SQLServer logs,how much time the databases
need to come up,on every start the database are checked while opened if any
inconsictencys exsists. If so,the database is "repaired" (open transactions
are rolled back,etc.). The betteryou leave the system duringthe shutdown the
faster it gets up on a restart.
SQL Server logs can be found in the Control node of SQL Server -- Current
Log.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Arora Smith" <Arora_Smith@.hotmail.com> schrieb im Newsbeitrag
news:%233MaZErfFHA.460@.TK2MSFTNGP09.phx.gbl...
> When I start the SQL Server by command prompt for single user mode it is
> taking lots of time(15 minutes) to start the SQL Server. My computer is a
> slow system but it is not very bad slow computer.
> IS it a resonable time or not?
> SQL 2K.
> Thanks,
> Smith
>
>
Friday, March 9, 2012
Managing Stored Procedures / Functions for releases.
WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase.
What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script.
A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure. Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release.
We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests...
What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us.
We have 8 developers in our team.
If anyone could help or give advice on this, it would be awesome.
Thanks.
You need some form of source control system. If you have Visual Source Safe then put the DROP/CREATE scrips in it and before any developer changes an SP they make sure it isn't checked out. They check it out, do some work, then check the new update script in.If you are all using different databases the developer should run the script that is in Source Safe against their database before making changes to ensure they are working on the latest version.
|||Thanks, That is currently what we're switching to.
I wanted to know if there's anything better we should be doing :D
now we need to write a file to throw them all together.
Wednesday, March 7, 2012
Managing reporting services models
Hi everybody,
I have the following scenario: I have web application which is creting new SQL Server database each time when new customer is created in the application.
I would like to give users the possibility of creating ad hoc reports and thus I need to create new connection and report model (and deploy them to my reoport server) each time when the new database is created (report models can not use multiple databases). Does anybody knows how can I do that?
Maybe there is another approach to this kind of problem?
Thank you in advance,
Marek
You can create datasources and autogenerate models from these datasources using the SSRS SOAP API. Check out this article in BOL for information on how to get started using the SOAP API:
http://msdn2.microsoft.com/en-us/library/ms159162.aspx
Subscribe to:
Posts (Atom)