Adding a new parameter to a subreport

Each time we add a new parameter to a subreport and we link the main report's fields to be the value's to the parameters for the subreport, we get an error message stating that the parameter for the subreport has not been specified. If we physically REBOOT the computer and then reload the project, the error seems to clear up.

Is this a bug in Reporting Services or is there some other explaination.

Thanks for the information.

Actually the problem was that the developer added a parameter, but did not convert the value into an integer (cint). Sorry for the incorrect post.sql

Adding a BIT column - 100+ hours!

I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.

Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?

Any ideas?ALTER TABLE ADD...NOT NULL DEFAULT... is a fully logged operation. The logs that contain internal page updates are small but numerous (and I use this word loosely). There is nothing you can do at this point because as you mentioned correctly, - the rollback will take even longer. And as I stated in one of my replies to you previous similar post, - if you kill the service the database recovery will take forever (weeks possibly). There is however a rather drastic approach that may (!!!) yield a quick return your database to pre-ALTER state...I would not recommend you trying it simply because it may or may not succeed, and if it doesn't, - someone is gonna be fired...|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......
I'd hope so...Did they?|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?

From a design perspective, the column should be non-nullable. I'm sure we could deal with it as a NULLable column if absolutely necessary. That's a sacrifice I'd easily be willing to make but at this point it's too late.

As I said, I didn't do this and it's frustrating from my perspective. We really didn't need the column on this table in the first place and it was a without question a bad move. If he started both column add operations at the same time before he realized what a drastic operation this would be then I can understand his perspective. Believe me, I am using the info I learn here, and trying to convey what I learn to my team.

Thanks you both for the feedback! I will not cancel, let it run, just cross my fingers that it finishes in the next week, and I will try to convince my boss to give me yet another database server so that we can start over (not fun) as a parallel strategy. Thankfully, this was done on a secondary system where we are trying to reconstruct our primary database with a slightly improved process (bug fixes and better data gathering).|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......

LOL! No, no backups.

For some reason I feel way more stressed about this than either my boss or the worker who is assisting on this project.|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?

"The other" way? :) Mysterious...

I just emailed our part time DBA but I'm not counting on much. Unfortunately, we are mostly programmers.|||Threads like this just make me speechless.

It's hard to talk when you're rolling on the floor laughing.sql

Adding a article causes ALL articles to be refreshed

I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.

This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.

Has that functionality changed from 2000 to 2005? Am I not doing something right?

Thanks,

Amir

This is the default behavior for anonymous subscribers. IIRC through the GUI for named subscribers a complete snapshot will be generated. However if you use sp_addarticle only the mini snapshot for the one table will be generated.|||

I have tried using sp_article. The problem is that it gives me the error:

Cannot make the change because a snapshot is already generated. Set @.force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

And in order to avoid the error, if I use the Option of @.force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything.

BTW, What does IIRC mean?

Thanks,

Amir

|||

Any more thoughts on this? If my replication is setup and running, is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables? Adding a table to replication seems one of the most basic maintenance tasks. I'm still surprised that MSFT has made that basic task so difficult in 2005.

Adding "From Query" parameters degrades performance

Hi list,
This is the second time I've posted this question, the second time I've
created a report with "From Query" parameters and the second time I've had
problems with performance with the "From Query" parameters...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
Here's the question I posed before, unfortunately I've recieved no
response.. Can someone help, has anyone else noticed the same problem...?
The problem I'm having is very easy to reproduce...
On the main menu in report designer... In the Report->Report Parameters
property page there are two types of
parameters Non-Queried and From Query. Non-Queried appears to prompt the
user for its value while the From Query parameter appears to be populated
from the query as it's name implies by assigning a column from the dataset.
I've created several of these type of parameters because I need a way to
have a page header with data from the query repeat on every page since I
cannot put Fields in the page header and using a table with the
repeatonnewpage doesn't seem to work this was my only viable solution. If
you look at my posts for the last couple days you'll get an idea of what I'm
trying to do. So my question remains why would creating From Query
parameters slow the execution of my report...? Am I using them incorrectly
is there another direction I can take to to achieve my goal...
Thanks,
DanI use this all the time without any performance problem whatsoever. A few
questions for you. How many parameters do you have in the report? How many
rows are being returned? How long does it take for each of them to run from
outside of RS (from Query Analyzer).
Am I right in assuming the time taken is for the report to come up and
prompt for the parameters? It is not when you try to then view the report is
it?
Try a report that does nothing except have the report parameters based on
the queries. This would help pinpoint if it is the parameters or something
else.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>|||Hi Bruce,
Thanks for your reply, in this particular report I have 47 "From Query"
parameters which is less than the previous report I built which seemed to
take longer. For the report I'm working on now I have a header and a
subreport, the header returns one row of data and contains the from query
parameters. Strangely enough the troubles I was having with the header
report have seemed to clear up, although the first couple of times I tried
to run the report the report previewer indicated that my report had been
canceled and then I would click the View Report button again and the report
would run. My first report returned several rows of data but the data was
repeated for every line of detail this is the one that I'm continually
having performance problems with I'm assigning the repeated data to the From
Query parameters.
Dan
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> I use this all the time without any performance problem whatsoever. A few
> questions for you. How many parameters do you have in the report? How many
> rows are being returned? How long does it take for each of them to run
from
> outside of RS (from Query Analyzer).
> Am I right in assuming the time taken is for the report to come up and
> prompt for the parameters? It is not when you try to then view the report
is
> it?
> Try a report that does nothing except have the report parameters based on
> the queries. This would help pinpoint if it is the parameters or something
> else.
> Bruce L-C
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > Hi list,
> >
> > This is the second time I've posted this question, the second time I've
> > created a report with "From Query" parameters and the second time I've
had
> > problems with performance with the "From Query" parameters...
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> >
> > Here's the question I posed before, unfortunately I've recieved no
> > response.. Can someone help, has anyone else noticed the same
problem...?
> > The problem I'm having is very easy to reproduce...
> >
> > On the main menu in report designer... In the Report->Report Parameters
> > property page there are two types of
> > parameters Non-Queried and From Query. Non-Queried appears to prompt the
> > user for its value while the From Query parameter appears to be
populated
> > from the query as it's name implies by assigning a column from the
> dataset.
> > I've created several of these type of parameters because I need a way to
> > have a page header with data from the query repeat on every page since I
> > cannot put Fields in the page header and using a table with the
> > repeatonnewpage doesn't seem to work this was my only viable solution.
If
> > you look at my posts for the last couple days you'll get an idea of what
> I'm
> > trying to do. So my question remains why would creating From Query
> > parameters slow the execution of my report...? Am I using them
incorrectly
> > is there another direction I can take to to achieve my goal...
> >
> > Thanks,
> >
> > Dan
> >
> >
> >
>|||One other issue to look at as well is whether the performance issue is seen
in only the development environment versus both development and after
deployment. That would be one other issue to consider.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:uxjpJzrjEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi Bruce,
> Thanks for your reply, in this particular report I have 47 "From Query"
> parameters which is less than the previous report I built which seemed to
> take longer. For the report I'm working on now I have a header and a
> subreport, the header returns one row of data and contains the from query
> parameters. Strangely enough the troubles I was having with the header
> report have seemed to clear up, although the first couple of times I tried
> to run the report the report previewer indicated that my report had been
> canceled and then I would click the View Report button again and the
report
> would run. My first report returned several rows of data but the data was
> repeated for every line of detail this is the one that I'm continually
> having performance problems with I'm assigning the repeated data to the
From
> Query parameters.
> Dan
>
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > I use this all the time without any performance problem whatsoever. A
few
> > questions for you. How many parameters do you have in the report? How
many
> > rows are being returned? How long does it take for each of them to run
> from
> > outside of RS (from Query Analyzer).
> >
> > Am I right in assuming the time taken is for the report to come up and
> > prompt for the parameters? It is not when you try to then view the
report
> is
> > it?
> >
> > Try a report that does nothing except have the report parameters based
on
> > the queries. This would help pinpoint if it is the parameters or
something
> > else.
> >
> > Bruce L-C
> >
> >
> > "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
> > in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > > Hi list,
> > >
> > > This is the second time I've posted this question, the second time
I've
> > > created a report with "From Query" parameters and the second time I've
> had
> > > problems with performance with the "From Query" parameters...
> > >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> > >
> > > Here's the question I posed before, unfortunately I've recieved no
> > > response.. Can someone help, has anyone else noticed the same
> problem...?
> > > The problem I'm having is very easy to reproduce...
> > >
> > > On the main menu in report designer... In the Report->Report
Parameters
> > > property page there are two types of
> > > parameters Non-Queried and From Query. Non-Queried appears to prompt
the
> > > user for its value while the From Query parameter appears to be
> populated
> > > from the query as it's name implies by assigning a column from the
> > dataset.
> > > I've created several of these type of parameters because I need a way
to
> > > have a page header with data from the query repeat on every page since
I
> > > cannot put Fields in the page header and using a table with the
> > > repeatonnewpage doesn't seem to work this was my only viable solution.
> If
> > > you look at my posts for the last couple days you'll get an idea of
what
> > I'm
> > > trying to do. So my question remains why would creating From Query
> > > parameters slow the execution of my report...? Am I using them
> incorrectly
> > > is there another direction I can take to to achieve my goal...
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> > >
> >
> >
>|||I think the entire query is first run without parameters. Then from the
results of the query, the parameters are retrieved making them available for
selection. That would be why you would see a performance issue, especially
with larger sets of data.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>

Addicted to Stored Procedures: A time series problem. Is it possible?

It's been about two weeks since I first began using SQL Stored procedures and now I am thinking already about changing many of my plans and doing those operations in them. There is a problem I want to address and see if it is possible to accomplish it in SQL Server instead of the client C# code.
I have a time series. In simplified presentation it is a table of two columns. The first one is float, the second is DateTime. There might be quite a few rows in the table:

A1 ... DT1
A2 ... DT2
................
An.... DTn
What I need to do is to find patters among A1,A2,....An elements. They vary in magnitude in a rather random order. They constitute what some would call a Markov chain. I demonstrate what I want in a couple of real life examples that I would not have a problem to run in my C# code at all.

(1) element Ak may be only 90% of the element in the previous row Ak-1. I want to catch it. I want to get the DateTime stamp when it happened. The next step is: I want to go down the chain of rows and find the element Am which rebounds to the magnitude of the element Ak. In other words Am==Ak. I want to remember its DateTime stamp.Then I want to find all elements in the chain of rows that follow the same pattern.

How can I do it in SQL language? Is it possible? I've been thinking about creating a bunch of temporary tables with rows shifted up by one and and destroying them after a pattern has been found in the same rows of many tables.

(2) I want to collect a distribution of many such patterns and analyze then in C# code but the elementary block is the operation I described in point (1). If I could get a cue as to how to go about it I could figure out the rest, I hope.

I learn by examples, by looking at code samples. So far I haven't seen anything that fits this task. If anyone could send me in such direction it would be great. Most of the things I've seen is "SELECT FROM CUSTOMERS," etc.

A somewhat related question. Years ago I stumbled in MSN help on a set of Math routines (they could have been part of a class) that were designed specifically for analyzing patters. It is not the Math class, I have looked into it. It is something different. I cannot find them now. I did not save a bookmark back then.

Does anyone know what I am talking about: math functions for time series analysis?

Thanks.

Time Series Analysis is actually a part of Data Mining for SQL Server, I just found out, but since I have an SQLEXPRESS I probably cannot use it. Right? I have to get the full server for that?|||

OK, I've been trying to write a stored procedure with some SQL statements that conceivably could handle the task at hand.

This is what I've come up with. It is assumed that there is a table tick_M, it contains two columns: bid and dateTimed, I create a temporary table tempTable1 with three columns. In addition to bid and dateTimed there will be a RowNumber column. Thus we end up with 3 columns. Then I want to create yet another table from temporary tempTable1 which will consist of all the rows of the first table starting from row # 2. Then I want to make a LEFT INNER JOIN ON rownumber1 = rownumber2.

This is the code.

WITH tempTable1 AS
( SELECT ROW_NUMBER() OVER (ORDER BY dateTimed) AS 'RowNumber1',
bid OVER AS 'bid1',
dateTimed OVER AS 'dateTimed1'
FROM tick_M )
GO
WITH tempTable2 AS
( SELECT ROW_NUMBER() OVER (ORDER BY dateTimed1) AS 'RowNumber2',
bid1 OVER AS bid2,
dateTimed1 OVER AS dateTimed2
FROM tempTable1 WHERE RowNumber1 > 1)
GO
SELECT tempTable1.bid1, tempTable1.dateTimed1, tempTable1.RowNumber1,
tempTable2.bid2, tempTable2.dateTimed2
FROM tempTable1 AS Result
LEFT OUTER JOIN tempTable2 ON tempTable1.RowNumber1 =
tempTable2.RowNumber2
GO

The trouble is I get compilation errors. Could anyone help me here?

Thanks.

|||

Alex,

I neither understood nor thoroughly checked your code however at first glance I see some syntax errors. Remove "go" spread in your SQL. CTEs do not have a go after them. ie:

with tempTable1 as ( ... ),
tempTable2 as ( ... ) ...

|||

CetinBasoz wrote:

Alex,

I neither understood nor thoroughly checked your code however at first glance I see some syntax errors. Remove "go" spread in your SQL. CTEs do not have a go after them. ie:

with tempTable1 as ( ... ),
tempTable2 as ( ... ) ...

Cetin hi,

It gives me an error even before a GO. It says "Incorrect syntax near OVER" pointing to the first OVER. I removed the GOs.

Thanks for your help.

|||

Yes I'd error too:) Why do you have multiple over there (you might - but then you should also have an partiotion and/or order by clause to use with over).

Anyway Alex,

I think you should rephrase your original question with sampling (and prefarably with ready to generate code for sample data). I didn't understand it and probably no one did.

Just cleared code a bit w/o checking extensively.

WITH tempTable1 AS
( SELECT ROW_NUMBER() OVER (ORDER BY dateTimed) AS 'RowNumber1',
 bid AS 'bid1',
 dateTimed AS 'dateTimed1'
 FROM tick_M )
WITH tempTable2 AS
( SELECT ROW_NUMBER() OVER (ORDER BY dateTimed1) AS 'RowNumber2',
 bid1 AS bid2, 
 dateTimed1 AS dateTimed2 
 FROM tempTable1 WHERE RowNumber1 > 1)
SELECT tempTable1.bid1, tempTable1.dateTimed1, tempTable1.RowNumber1,
 tempTable2.bid2, tempTable2.dateTimed2 
FROM tempTable1 AS Result
 LEFT OUTER JOIN tempTable2 ON tempTable1.RowNumber1 = 
 tempTable2.RowNumber2 

|||

CetinBasoz wrote:

Yes I'd error too:) Why do you have multiple over there (you might - but then you should also have an partiotion and/or order by clause to use with over).

Anyway Alex,

I think you should rephrase your original question with sampling (and prefarably with ready to generate code for sample data). I didn't understand it and probably no one did.

Cetin hi,

Thank you for the code. Unfortnately it did not comply. I reworked the code entirely and came up with something that did:

USE hist_Tick
GO
DROP TABLE #tempTable_1
GO
SELECT ROW_NUMBER() OVER (ORDER BY dateTimed) AS 'RowNumber1',
bid AS 'bid1',
dateTimed AS 'dateTimed1'
INTO #tempTable_1
FROM dbo.tick_MSFT
GO
DROP TABLE #tempTable_2
SELECT ROW_NUMBER() OVER (ORDER BY dateTimed1) AS 'RowNumber2',
bid1 AS 'bid2',
dateTimed1 AS 'dateTimed2'
INTO #tempTable_2
FROM #tempTable_1
WHERE RowNumber1 > 1
GO
DROP TABLE #tempTable_3
GO
SELECT DISTINCT bid1, dateTimed1, RowNumber1, bid2, dateTimed2
INTO #tempTable_3
FROM #tempTable_1, #tempTable_2
WHERE RowNumber1 = RowNumber2
ORDER BY dateTimed1
GO
SELECT * FROM #tempTable_3
GO

The idea was to create a JOIN table with values from two original tables in such a way that the second table has all rows (values) shifted by one row forward (down). The combination table naturally should be one row shorter than the original tables. I got displayed #tempTable_3. It looks perfect.

Thanks.

|||

I see what you mean now:) It could be done even with VFP SQL using a single query joining the table with itself :) Would this do?

select
t1.bid as bid1, t1.datetimed as datetimed1, t2.bid as bid2, t2.datetimed as datetimed2
from timedSeries t1 left join timedSeries t2 on t1.DateTimed < t2.dateTimed
where t2.DateTimed = (select min(datetimed) from timedSeries where dateTimed > t1.dateTimed) or t2.dateTimed is null

Or this one with CTE:

with tSeries as ( select row_number() over (order by dateTimed) as rowNum, bid, datetimed from timedSeries )
select
t1.bid as bid1, t1.datetimed as datetimed1, t2.bid as bid2, t2.datetimed as datetimed2
from tSeries t1 left join tSeries t2 on t1.rowNum = t2.rowNum - 1

|||

Cetin hi,

Thank you very much. I switched to SQLEXPRESS because I could not handle a simple problem in C# dealing with VFP: I could not figure out how to write into the tables. I could read OK, could write into VFP tables, update them using XML but could not write directly. The mapping of columns killed me. Now I do not regret that I switched to SQL Server. I think, overall, it will be a more appropriate server for me.

But I will look into your code. I am also discovering additional commands in Transact-SQL which possibly will be even more helpful for me in this task.

Incidentally, I ran into a problem dropping the temporary tables in that query that I showed. When the query runs the first time DROP TABLE commmand causes an error. Conditional statement IF OBJECT_ID ('#tempTable_1') IS NOT NULL before it did not do anything. It appears SQL server ignores it during execution.

Thanks.

|||

Writing to VFP tables are straight forward but I didn't mean why you chose SQLEXPRESS:) I too like MSSQL and trying to learn it in depth. I meant that SQL would even work within VFP and supported since years (remember VFP supports much less SQL syntax than MSSQL does).

I don't think you need to drop a temp table there and also I don't think you need a temp table.

|||

Well, in this case, I am done with that stored procedure (DROPs aren't needed is a good news). I am actually having second thoughts on that. I think I can do FETCH row in a loop with cursors. I did not realize it until today. Now I know. It is all I need. I will rectify my knowledge in that area, write a few queries and, hopefully, it will take off. I am especially pleased that a bulk of calculations could be done at the database.

Now, with VFP I still do not understand how I can use it in here since now all my calculations, data retreival from a network server is done in C#. I use MSSQL for data storage and hopefully for most of analysis of historical data. Some analysis will be done in C# but that will be real time data analysis. I need to interact with the SQL server almost all the time but I could not easy interact with VFP database--it was all onesided. Although I do have another project still unfinished. I may use VFP database for the other one all the way. That project is also in C# but over there the data flow is slow and interactive and the volume is small.

Thanks.

|||

If data flow is slow then you don't need VFP:)

We are having a communication problem here. I didn't mean "do it in VFP". I meant those 2 SQL code would work better than your temptable approach. Try them and choose one.

Add YTD for number of cubes?

Hello,

We have number of cubes sharing one Time dimension; we need add YTD, QTD, and MTD to each cube, is there a easy way to add YTD, QTD to Time dimension one time instead of add them to the cube one by one?

Thanks in advance for the advice.

Yes - you can add the member holders for YTD, MTD, QTD etc to the corresponding attribute of the Time dimension, and add all the MDX formulas to the dedicated column in the dimension table, and the use CustomFormulaColumn property on the attribute to point to that column. After that, every cube which includes this dimension will have all these formulas automatically.|||Thank you very much Mosha for the help.

1. add YTD column to the Time dimension
2, add all the MDX formulas to the YTD column
(
[All Periods].[YTD].[Year to Date],
[All Periods].[Year].[Year].Members,
[All Periods].[PK Date].Members
) =

Aggregate(
{ [All Periods].[YTD].DefaultMember } *
PeriodsToDate(
[All Periods].[Year - Quarter - Month].[Year],
[All Periods].[Year - Quarter - Month].CurrentMember
)
);

You mean all these code add into column?
3, use CustomFormulaColumn property on the attribute.
where can I find 'CustomFormulaColumn' property?
I checked BI Studio, and could not find that.

Thanks.
|||

You can only put the MDX expression, not the MDX statement into this column So in your case it would be the right hand side of the assignment.

The real name of the attribute's property is CustomRollupColumn.

|||I put MDX expression in YTD column, and point CustomRollupColumn property to All_Periods.YTD. After processing, YTD just show the text which are the 'MDX expression' I put in and measure.order became '#VALUE'.

What else I am missing?

Thanks.
|||I did Google search for CustomRollupColumn Property, some people said that it's work in SSAS 2000, but not in SSAS 2005. Is it true?

|||CustomRollupColumn property works fine in SSAS 2005. Probably it is the best for you to look at example to understand how it should be set up. In the Adventure Works sample, take a look at Accounts dimension, Accounts attribute - it has custom rollups defined.

Add YTD for number of cubes?

Hello,

We have number of cubes sharing one Time dimension; we need add YTD, QTD, and MTD to each cube, is there a easy way to add YTD, QTD to Time dimension one time instead of add them to the cube one by one?

Thanks in advance for the advice.

Yes - you can add the member holders for YTD, MTD, QTD etc to the corresponding attribute of the Time dimension, and add all the MDX formulas to the dedicated column in the dimension table, and the use CustomFormulaColumn property on the attribute to point to that column. After that, every cube which includes this dimension will have all these formulas automatically.|||Thank you very much Mosha for the help.

1. add YTD column to the Time dimension
2, add all the MDX formulas to the YTD column
(
[All Periods].[YTD].[Year to Date],
[All Periods].[Year].[Year].Members,
[All Periods].[PK Date].Members
) =

Aggregate(
{ [All Periods].[YTD].DefaultMember } *
PeriodsToDate(
[All Periods].[Year - Quarter - Month].[Year],
[All Periods].[Year - Quarter - Month].CurrentMember
)
);

You mean all these code add into column?
3, use CustomFormulaColumn property on the attribute.
where can I find 'CustomFormulaColumn' property?
I checked BI Studio, and could not find that.

Thanks.
|||

You can only put the MDX expression, not the MDX statement into this column So in your case it would be the right hand side of the assignment.

The real name of the attribute's property is CustomRollupColumn.

|||I put MDX expression in YTD column, and point CustomRollupColumn property to All_Periods.YTD. After processing, YTD just show the text which are the 'MDX expression' I put in and measure.order became '#VALUE'.

What else I am missing?

Thanks.
|||I did Google search for CustomRollupColumn Property, some people said that it's work in SSAS 2000, but not in SSAS 2005. Is it true?

|||CustomRollupColumn property works fine in SSAS 2005. Probably it is the best for you to look at example to understand how it should be set up. In the Adventure Works sample, take a look at Accounts dimension, Accounts attribute - it has custom rollups defined.sql

add two time hierarchies to a dimension?

I got problem when trying to add two exactly same time hierachies to a
dimension.
There are two fields StartTimeID, and EndTimeID, which point to Time
dimension table by TimeID. I would like to use these two time hierachies to
restrict my query.
Something like SQL: Select ... from ... where StartTimeID > 1000 and
EndTimeID < 2000 for a time range specified.
I am testing this. Is this possible in MDX after I have the hierachies?
Guangming
I found the solution: add one more named set (like View) of Time table and
join these two time ID fields to these two Time dimension tables. Everything
should be ok.
"Word 2003 memory Leakage" wrote:

> I got problem when trying to add two exactly same time hierachies to a
> dimension.
> There are two fields StartTimeID, and EndTimeID, which point to Time
> dimension table by TimeID. I would like to use these two time hierachies to
> restrict my query.
> Something like SQL: Select ... from ... where StartTimeID > 1000 and
> EndTimeID < 2000 for a time range specified.
> I am testing this. Is this possible in MDX after I have the hierachies?
>
> Guangming
|||I think you will still have issues, the two MDX ranges will be evaluated
independantly, effectively resulting the equivalent of a logical OR
between the two conditions.
eg: Select ... from ... where StartTimeID > 1000 OR
EndTimeID < 2000 for a time range specified.
Because multiple rows in the fact table typically rollup into a single
cell in the cube these sort of queries are difficult to implement in a
cube.
I don't know if anyone else out there has any experience with this?
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

add two time hierarchies to a dimension?

I got problem when trying to add two exactly same time hierachies to a
dimension.
There are two fields StartTimeID, and EndTimeID, which point to Time
dimension table by TimeID. I would like to use these two time hierachies to
restrict my query.
Something like SQL: Select ... from ... where StartTimeID > 1000 and
EndTimeID < 2000 for a time range specified.
I am testing this. Is this possible in MDX after I have the hierachies?
GuangmingI found the solution: add one more named set (like View) of Time table and
join these two time ID fields to these two Time dimension tables. Everything
should be ok.
"Word 2003 memory Leakage" wrote:

> I got problem when trying to add two exactly same time hierachies to a
> dimension.
> There are two fields StartTimeID, and EndTimeID, which point to Time
> dimension table by TimeID. I would like to use these two time hierachies t
o
> restrict my query.
> Something like SQL: Select ... from ... where StartTimeID > 1000 and
> EndTimeID < 2000 for a time range specified.
> I am testing this. Is this possible in MDX after I have the hierachies?
>
> Guangming|||I think you will still have issues, the two MDX ranges will be evaluated
independantly, effectively resulting the equivalent of a logical OR
between the two conditions.
eg: Select ... from ... where StartTimeID > 1000 OR
EndTimeID < 2000 for a time range specified.
Because multiple rows in the fact table typically rollup into a single
cell in the cube these sort of queries are difficult to implement in a
cube.
I don't know if anyone else out there has any experience with this?
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

add time to datetime value and split into date and time

Hi

i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.

the timezone field has values in minutes that i should add to my datetime field so i have the actual time.

afterwards i split the datetime into date and time.

the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).

could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?

i do all the calculations in my datasource (sql).

Thanks

V.

at the end i found it myself

this is how i solved it.

to add the timezone value (in minutes) to my datetime i used following :

DATEADD(minute, TimeZone, DATETIMEVALUE) AS ACTUALDATETIME

this expression will add the timezone value (example 60) to my current date time value (12/06/2007 08:00:00) and store it in

actualdatetime value (12/06/2007 09:00:00).

from then on it's easy to extract date and time from the result.

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 103) AS DATEVAL for the DATE

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 108) AS TIMEVAL for the time.

i thank myself for my research .. lol

Greetings to all

add seconds to time.

Hello how would i add 5 seconds to this value in a select statement?
2005-02-16 04:12:44.000Use DATEADD function.
declare @.d datetime
set @.d = '2005-02-16 04:12:44.000'
select dateadd(second, 5, @.d)
go
AMB
"Fab" wrote:

> Hello how would i add 5 seconds to this value in a select statement?
> 2005-02-16 04:12:44.000
>
>

add event to application log

How do I add events to be recorded in the application log? I have a process
that is not completing 100% of the time, but I am not getting any events
recorded in the application log. The severity must be < 20. I realize that
opeing this up to all events will cause a lot of activity, but I need to fin
d
out why this process does not complete every time it is run.
Thanks in advance.Doug,
Check out:
sp_addmessage
RAISERROR ... WITH LOG
xp_logevent
The choice will depend on your approach.
HTH
Jerry
"doug" <doug@.discussions.microsoft.com> wrote in message
news:310BB4E6-F9B4-4907-948A-7715B3289C04@.microsoft.com...
> How do I add events to be recorded in the application log? I have a
> process
> that is not completing 100% of the time, but I am not getting any events
> recorded in the application log. The severity must be < 20. I realize
> that
> opeing this up to all events will cause a lot of activity, but I need to
> find
> out why this process does not complete every time it is run.
> Thanks in advance.

add event to application log

How do I add events to be recorded in the application log? I have a process
that is not completing 100% of the time, but I am not getting any events
recorded in the application log. The severity must be < 20. I realize that
opeing this up to all events will cause a lot of activity, but I need to find
out why this process does not complete every time it is run.
Thanks in advance.Doug,
Check out:
sp_addmessage
RAISERROR ... WITH LOG
xp_logevent
The choice will depend on your approach.
HTH
Jerry
"doug" <doug@.discussions.microsoft.com> wrote in message
news:310BB4E6-F9B4-4907-948A-7715B3289C04@.microsoft.com...
> How do I add events to be recorded in the application log? I have a
> process
> that is not completing 100% of the time, but I am not getting any events
> recorded in the application log. The severity must be < 20. I realize
> that
> opeing this up to all events will cause a lot of activity, but I need to
> find
> out why this process does not complete every time it is run.
> Thanks in advance.

Add days to all datetime columns in the database

Hi,
I got a request from our Sr. Director regarding demo data.
He's stated that demo databases tend to age with time. As
you enter orders they become older and older as time
passes.
Anyways, he's asking how difficult would it be to write a
SQL utility that asks the user for a number of days and
then ran through the ENTIRE database looking for EVERY
Date/Time column and add the number of days to the data
content of the field.
I've sort of created already somewhat a DML command that
will autogenerate for me the update statement, but is
there a way for me to prompt the user in Query Analyzer
for a value to set the variable? Does it have to be done
via command prompt?
I've copied below my DML statement that I've quickly
written up.
select 'update ' + OBJECT_NAME(id) + ' set ' + substring
(name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
OBJECT_NAME(id)
from syscolumns
where name like '%Date%'
order by OBJECT_NAME(id)
TIA,
BettinaYou can make a template in Query Analyzer ...
You can insert tags like: <myTag, varchar(20), 'abc'> (Structure is:
<TagName, DataType, Default>)
Then just hit CTRL-SHIFT-M and it will prompt for replacement. Very handy.
You can also save your template in the <SQL Server
Root>\80\Tools\Templates\SQL Query Analyzer\ folder, as a .TQL file, and
when you click on the Templates tag in Query Analyzer it will be there for
you to use.
Or you could just make a stored procedure...
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>|||In the databases I see you will pick up lots of things that don't correspond
to an SQL datetime data type.
If you get this syscolumns.xtype contain they system type of the column.
syscolumns.xtype in (58, 61) will pick all datetime columns.
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>

Add date to record in SQL server each time record is added

Hi

Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?

thanks

Sure, you can do it a number of ways. Add a datetime field to your table, and set it's default value to either GetDate() or GetUTCDate(). That's one approach, but it only works for inserts.

If you want to have it updated on every update, then you will need to implement that in a trigger, or modify your UPDATE statements to set the field to GetDate() or GetUTCDate().

|||

Cool, I'll try that.

I want it INSERTED once for every record so i think the first way is the way to go.

Tell me this: do i need to add any code to the web form or is it enough to add the field to the db table and set the values?

Thanks!!

|||if you add a datetime column to a table, and set it's default value to getdate(), then whenever you insert a row into that table, the new column will have the date and time that the row was inserted. You don't have to change any SQL Statements at all, unless you've specifically created a dynamic update statement that will now attempt to insert a value into the new column, or something similiar. In most cases (I'll guess 95%+) nothing needs be changed at all.|||Cool. That is just perfect. No change needed at all. Thanks for your help!!|||I tried using the getdate() as the default; however, the date always comes out to be 1/1/1900. Any advise?|||

Haywire:

I tried using the getdate() as the default; however, the date always comes out to be 1/1/1900. Any advise?

What's the data type of the column? It should be be datetime.

Add Date only to Time only = datetime ?

I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
CarlIf I understand you, you want use date from EVT_DT, use time from EVT_TM put them together and retrieve datetime: '2005-12-05 13:59:00'

if I'm right, transform dates to varchar, cut what u need, concatenate strings and transform it back to date time:

declare @.EVT_DT datetime
declare @.EVT_TM datetime

set @.EVT_DT = '2005-12-05 00:00:00'
set @.EVT_TM = '2005-12-06 13:59:00'

select
cast(
cast(@.EVT_DT as varchar(11)) + ' ' +
cast(datepart(hh, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(mi, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(ss, @.EVT_TM) as varchar(2))
as datetime)

2005-12-05 13:59:00.000|||I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
Carl

Just so you are aware, using your examples above, SQL would "treat" EVT_DT as 38689.00000 and EVT_TM as 38690.58264. So, when you try to add them together, you're getting an unexpected value. A better practice would be to store the completed Date/Time together in a single column and then deal with the issue of selecting for a specific data by using BETWEEN.

Regards,

hmscott

add Date and Time In Non english format(Farsi or arabic)

Please Help me
I want Store date and time in sql server tables but in non english format.
For example Arabic or farsi
How can i Do?
Help meeeeeeeee
ThanksI am not sure which format you want - but there are 2 Kuwaiti formats:

Style Format
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mon/yy hh:mi:ss:mmmAM

You can alter your datetime data using the convert function. Look at convert in bol.|||Date and time are not stored in any "format" in the database. They are stored as numeric values indicating the days elapsed since a fixed point in time. What you are looking for is a way to display the datetime value in a particular format. You can use the convert function given by rnealejr when outputing your results, but generally issues of data-formatting and display should be handled by your user interface or reporting tool.

blindman

add and subract time to a date

Hello all,
Lets say I have the current date and I would like to get the date
two weeks in advance. How would I do this in T-sql? The T-SQL should
take into account if the month changes. For example, if todays date
is 1/17/2003 the advanced date would be 2/17/2003.

Thanks,
BillyYou can use DATEADD function to accomplish this. Refer to SQL Server Books
Online for more details. For example :

SELECT DATEADD(m, 1, '20030117') ;

--
- Anith
( Please reply to newsgroups only )

Add additional Subscribers

Hi all,
Have Transactional Replication on SQL 2000 EE SP4 for some time now as
follows:
Publisher/Distributor is an 8 way Server
8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
Dist Agents run at Distributor as PUSH to existing Subscribers as follows
Subscriber1 - ServerA
Subscriber2 - ServerB
Subscriber3 - ServerC
This works great, and I now want to ADD:
Subscriber4 - ServerD
Subscriber5 - ServerE
What I want to do is add Server D and E as Subscribers (these are all
separate physical servers) without having to re-initialize any data on
Servers A, B, or C which have been running just fine.
I can prevent access to the PUBLISHER database (~20GB) and quiesce the
Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if need
be.
What is the best way overall to approach this ?
When the Replication was first setup, I set the Snapshot Agent to use 8 BCP
Threads and it took about 30 mins to generate the Initial Snapshot, and
about 1.5 hours to apply to each Subscriber A, B, C across the network
(these servers are all well connected with dedicated nics on a private LAN
solely for Server to Server Replication traffic).
If I go through the Publication Properties dialog and ADD my two new
Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
follows:
1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E cause
the Snapshot Agent to actually create a fresh snapshot ? I assume it will
"invalidate", but there is no existing snapshot currently available, and the
SS Agent has only been run manually on occasion since the intitial setup to
publish new articles to the subscribers which has worked fine. Otherwise the
SS Agent remains disabled.
2 - Will the Snapshot attempt to be applied to, or mess up anything for the
existing (already in-synch) Subscriptions on Servers A, B, C or should it
*only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
I hope the above makes sense and ariticulates what I'm trying to do. Thanks
in advance for any advice, or hints/tips you can provide.
Thanks,
Frank
Just as a follow up question, if I have the Publisher DB and Subscriber DB's
all in synch, and not accessible to users can I:
1 - Take a FULL Backup of one of the Subscription DB on any of the existing
Subscriber Servers (A/B or C), then restore it to Servers D/E
2 - Use the Publication Wizard to PUSH a new Subscription to Servers D/E
using the No Synch option ?
Does that do all the necessary behind the scenes processing correctly ? I
assume because I've restored a backup of an existing Subscriber DB to the
NEW Subscriber DB that it will have all the INS, UPD, DEL StoredProcs in
place as well ?
I read you can do a backup/restore of the Publication DB on a Subscriber for
a no-synch initialization, but the Subscription DB's have auto created
ClusteredIndexes for the PriKeys that exist on the Publication so would that
be an issue ?
In addition, there are some different non-clustered indexes on the
Subscribers as they are used for read-only queries, and mostly reporting
purposes that are offloaded from running on the Publisher DB.
Thanks,
Frank
"Frank Conte" <Frank.Conte@.discussions.ms.com> wrote in message
news:%23GPqOqpwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Have Transactional Replication on SQL 2000 EE SP4 for some time now as
> follows:
> Publisher/Distributor is an 8 way Server
> 8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
> Dist Agents run at Distributor as PUSH to existing Subscribers as follows
> Subscriber1 - ServerA
> Subscriber2 - ServerB
> Subscriber3 - ServerC
> This works great, and I now want to ADD:
> Subscriber4 - ServerD
> Subscriber5 - ServerE
> What I want to do is add Server D and E as Subscribers (these are all
> separate physical servers) without having to re-initialize any data on
> Servers A, B, or C which have been running just fine.
> I can prevent access to the PUBLISHER database (~20GB) and quiesce the
> Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if
> need be.
> What is the best way overall to approach this ?
> When the Replication was first setup, I set the Snapshot Agent to use 8
> BCP Threads and it took about 30 mins to generate the Initial Snapshot,
> and about 1.5 hours to apply to each Subscriber A, B, C across the network
> (these servers are all well connected with dedicated nics on a private LAN
> solely for Server to Server Replication traffic).
> If I go through the Publication Properties dialog and ADD my two new
> Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
> follows:
> 1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E
> cause the Snapshot Agent to actually create a fresh snapshot ? I assume it
> will "invalidate", but there is no existing snapshot currently available,
> and the SS Agent has only been run manually on occasion since the intitial
> setup to publish new articles to the subscribers which has worked fine.
> Otherwise the SS Agent remains disabled.
> 2 - Will the Snapshot attempt to be applied to, or mess up anything for
> the existing (already in-synch) Subscriptions on Servers A, B, C or should
> it *only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
> I hope the above makes sense and ariticulates what I'm trying to do.
> Thanks in advance for any advice, or hints/tips you can provide.
> Thanks,
> Frank
>
|||(1) for an initialisation you'll need to create a new snapshot.
(2) initialization of the 2 new subscriptions won't affect existing
subscriptions.
HTH,
Paul Ibison
|||You can use a backup of the subscriber - it is much less work than taking the
publisher backup. You don't have to be concerned about identity columns,
deleteing data from filtred articles, removing redundant tables and so on.
There are more details which might be relevant here:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the quick reply, and helpful info.
So, according to section 2.1 "Adding Additional Articles" in the link you
provided; if I do this by way of backup an existing Subscriber, restore it
to a new Subscriber(s) than do a no-sych Subscription through the wizard I
LOSE the ability to run the snapshot agent thereafter where it would
ordinarily:
1 - Generate schema, index and data files for a NEWLY added article on the
publisher. For example, I add a new table on the Publisher, then go to the
Publication Properties and check off the new table to be added in the
Article tab. Normally I would run the Snapshot Agent manually and it
generated the files for just that newly added article to the Publication.
2 - Have the Distribution Agent pickup the new articles and push them to my
new Subscribers, like it would for my existing ones that were originally
initialized via Snapshot (sync_type=automatic).
If that is the case, I think I should take the other approach as per my
other POST ?
That is...add the two new Subscribers, generate a fresh snapshot, and let
it apply it to the two new Subscribers all while the Publication DB, and
existing Subscription DB's are quiesced and offline (as in no changes
allowed) ?
Hassan's workaround you noted doesn't apply if adding articles via the
Publication Properties, correct ? It would always require using
sp_addsubscription going forward, and as you mentioned make for a less
maintainable setup than I would like.
Thanks,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5C31C7A4-96EE-4DC5-963E-0D8751CA04B3@.microsoft.com...
> You can use a backup of the subscriber - it is much less work than taking
> the
> publisher backup. You don't have to be concerned about identity columns,
> deleteing data from filtred articles, removing redundant tables and so on.
> There are more details which might be relevant here:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
I think this is the better approach for me (see my other reply to yours) in
this thread.
So provided I do not delete and re-add my existing Subscribers in the
Publication Properties they will remain completely uneffected by the new
snapshot files that I do want applied to my new Subscribers.
That sounds like the way to go to keep the maintainable setup going forward.
Is there any other known workaround similiar to Hassan's that doesn't
require using scripting after using the backup/restore method instead of the
initialize with snapshot files ?
Many thanks for you insight, and on going assistance you provide in these
NG's and via your web site.
Regards,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:971720D2-2205-43B4-96B9-91FC56D37051@.microsoft.com...
> (1) for an initialisation you'll need to create a new snapshot.
> (2) initialization of the 2 new subscriptions won't affect existing
> subscriptions.
> HTH,
> Paul Ibison
|||Hi Frank - this is correct. For addition of new 'automatic' articles, you'd
need to do this in scripts. Therefore adding new subscriptions normally will
be more suitable. Actually I do the nosync method primarily when the
bandwidth is small and the snapshot is huge - in which case I zip it up
before FTPing over - quite different to your case as far as I can determine.
Cheers,
Paul Ibison
|||What I've seen some people do is to add the new articles to a new publication
in order for the process to be automatic (after having already done a nosync
initialization). This is fine for a one-off provided the articles aren't
related.
HTH,
Paul Ibison

Add a variable from custom component

Hi

I am writing a custom transformation component that utilises a user variable.

Before using the variable at run time I am checking that the variable exists, but it would be nice to be able to add it if it does not. I cannot find any documentation on the subject, though I can see that the Variables class is derived from a ReadOnlyCollectionBase.

Is there a way to add a user variable with package scope from a custom component, either at run time or design time?

Thanks . . . Ed

I do not think it is possible currently.

Thanks.