Showing posts with label parameter. Show all posts
Showing posts with label parameter. Show all posts

Monday, March 19, 2012

[SQL] One parameter and few values.

Hello!

I have tables "forum_topics" and "forum_categories":

forum_topics:
-topic_id
-topic_title
-topic_cat_id
-topic_user_id
-topic_date

forum_categories:
-cat_id
-cat_name

For example: I have 5 categories in table forum_categories and by this query I can display topics from 2nd and 3rd category only. So I'm using this query:
SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (2;3)

Now I want to parametrized this query by one parameter, so:

SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (@.param)

And I want to input this parameter lik in the query first - so @.param = "2;3"

But I have a problem to make this works, can someone explain me, how to solve this problem ? Thanks.

Czesc Grzesiek,

The quick and dirty way of doing it is to convert your SQL to a string and execute it later, this is useful when you have optional parameters. Your stor proc would look something like that:

Declare @.sqlas varchar(1000)SET @.sql ='SELECT topic_title FROM forum_topics WHERE topic_cat_id IN ('''+ @.param +''')'exec(@.sql)
|||

you want @.param to be comma separated value is that you are asking for ?

well you could do it in c# , vb.net or any language, could you tell me wht are you doing at the moment for that.

its simple just declare one string variable say str

string str = "";

if (str.length == 0)

str = some_value;

else

str += str +", " + some_other_value;

does that makes senseEmbarrassed

regards,

satish

|||

Hey Tetris - you're from Poland maybe ? :)

I have:

ALTER PROCEDURE ShowTopics

@.param varchar
AS

Declare @.sql as varchar(max)

SET @.sql='SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN ('''+@.param+'''))'
exec(@.sql)RETURN

All as you wrote. But when I write as @.param "1,2,3" I'm getting only topics (records) where cat_id is "1". If I choose "3,2,1" I will get only from 3rd category.

|||

I wanna add only that this query:

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN (1,2,3)

Returns me all topics from category 1st,2nd and 3rd. So my question is simply - how can I put (and how declare, or I don't know) in place 1,2,3 a parameter @.categories, and how make it works ?

|||

Oh God ...

My mistake was at the begin of stored procedure. There was:

ALTER PROCEDURE ShowTopics
@.param varchar()

Should be
ALTER PROCEDURE ShowTopics
@.param varchar(100)

(for example)

No it works !!!! Thanks Tetris ! Big thanks !

|||I'm glad you got it to work :) Yeh I'm polish but I live in Canada.|||

So I have one more question.

I have 5 checkboxes:
Checkbox1 (Category 1st),Checkbox2 (Category 2nd),Checkbox3 (Category 3rd),Checkbox4 (Category 4th),Checkbox5 (Category 5th).

And as cookie "Forum_kategorie" I have value = "1,3,5".

So I have topics from category 1,3 and 5. Do you know how can I easily checked checkbox 1,3 and 5 and unchecked second and fourth ?

Default value of "@.param" in this parametrized query above will be 1,2,3,4,5. And at first enter to site all five checkboxes will be checked. I will unchecked second and fourth and click the button, which will add/change cookie "Forum_kategorie" to value "1,3,5" and reload site. After that I will see topics from 1,3,5 category, but I have in some way to define which checkbox is un/checked.

Any ideas ?

Dzi?ki za pomoc :-) / Thanks for help :-)

|||If you are re-loading the same page, as long as you have EnableViewState = True for each checkbox, it should remember what it's last selection was when the site re-loads (on post back) Is this the case?|||

As you have a small rabge of values, take a look at using bitwise operators.

Your check box values would be bases on multiples of two: 1,2,4,8,16.

The value of the parameter that you pass into your stored procedure would be the sum of the values of the check boxes.

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id & @.Categories = forum_kategorie.forum_kat_id)

Sunday, March 11, 2012

[SELECT] question

Hi all,
I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [01], the third field the values of parameter code [02] and so forth??

Table structure
CREATE TABLE `test` (
`date` datetime NOT NULL ,
`code` varchar(3) NOT NULL ,
`value` float NOT NULL
) TYPE=MyISAM;

Sample data
+-------+--+---+
| date | code | value |
+-------+--+---+
| 2004-06-01 00:00:00 | 001 | 89 |
| 2004-06-01 00:00:00 | 002 | 1 |
| 2004-06-01 01:00:00 | 001 | 76 |
| 2004-06-01 01:00:00 | 002 | 5 |
| 2004-06-01 02:00:00 | 001 | 67 |
| 2004-06-01 02:00:00 | 002 | 7 |
| 2004-06-01 03:00:00 | 001 | 46 |
| 2004-06-01 03:00:00 | 002 | 4 |
| 2004-06-01 04:00:00 | 001 | 43 |
| 2004-06-01 04:00:00 | 002 | 3 |
+-------+--+---+

Recordset
+-------+----+----+
| date | value_cod1 | value_cod2 |
+-------+----+----+
| 2004-06-01 00:00:00 | 89 | 1 |
| 2004-06-01 01:00:00 | 76 | 5 |
| 2004-06-01 02:00:00 | 67 | 7 |
|..................................... ..............|
+-------+----+----+

Thank you very much!
Paolo SaudinTo select all of the columns in an arbitrary order, you can use:SELECT *
FROM testTo select the columns in a specific order, you can use:SELECT date, code, value
FROM test-PatP|||ah, the good old denormalization question

mysql doesn't support full outer join, but this is an equivalent --

select t1.date
, t1.value as value_cod1
, t2.value as value_cod2
from `test` as t1
left outer
join `test` as t2
on t1.date
= t2.date
and t2.code = '002'
where t1.code = '001'

union all

select t2.date
, null
, t2.value
from `test` as t1
right outer
join `test` as t2
on t1.date
= t2.date
and t1.code = '001'
where t2.code = '002'
and t1.date is null

you'll need mysql 4.0 to support union, and if you don't have any code 002 rows without a code 001 row, then you can get away with just the first of the two queries|||ah, the good old denormalization questionI wondered what on earth you were babbling about as I read your response, but when I went back and re-read the question carefully I discovered that you were correct! I've always preferred the structure:SELECT date
, Sum(CASE WHEN '001' = code THEN value END) AS '001'
, Sum(CASE WHEN '002' = code THEN value END) AS '002'
, Sum(CASE WHEN '003' = code THEN value END) AS '003'
FROM test
GROUP BY dateThis only has to make one pass through the table, so it can be more efficeient if the optimiser does its job.

-PatP|||i babble often

i'm wrong occasionally

:cool:|||Thank you all very much !! :)
Paolo Saudin

Saturday, February 25, 2012

[LOG] Unable to read local eventlog (reason: The parameter is incorrect) - FLOOD

[LOG] Unable to read local eventlog (reason: The parameter is incorrect)

I just had two seperate SQL Server 2005 Enterprise Edition machines suddently throw thousands of these errors within the last 24 hours. In both cases the the SQL agent and application event logs are just being flooded with a new entry every couple of seconds.

While the errors are being logged the Agent continues to run jobs (e.g. scheduled backups, etc.) without error.

A restart of the agent stops the errors and everything seems to be back to normal.

There is no commonality between the two machines, they are on different networks, domains, etc.

Both machines have been up >30 days and have not previously logged this error.

Anybody else seen this?

Thanks,

Joe

See this post Joe:

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

HTH,

Thursday, February 16, 2012

[bug?] Hidden parameters

Hello,
I have troubles with hidden parameters. What am I doing:
1) define a report parameter based on a dataset's field (a dataset is
"Stored proc." type, the procedure returns the only string value -- if it is
important)
2) Build & deploy a solution, run the report from Report manager -- it's ok
3) run the report from Report manager, click on "Properties" tab, choose
"parameters":
Has Default - leave checked on
Default Value - (Query Based)
Null - (none)
Prompt User - leave checked on
Prompt String - I cleared this textbox
4) run the report again:
Reporting Services Error
Default value or value provided for the report parameter 'MyParam' is not a
valid value. (rsInvalidReportParameter) Get Online Help
Refresh button on RM or the IE's same button don't change this error message
5) Properties -> Parameters -> Prompt String: type a former prompt string
6) run the report: it works ok, however the aforementioned parameter acts
like it doesn't have Default Value anymore.
What have I do to hide param properly?
(MS RS sp1)
Microsoft SQL Server Reporting Services Version 8.00.878.00
Thanks,
Lucy.Does it have list of available values?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have troubles with hidden parameters. What am I doing:
> 1) define a report parameter based on a dataset's field (a dataset is
> "Stored proc." type, the procedure returns the only string value -- if it
> is
> important)
> 2) Build & deploy a solution, run the report from Report manager -- it's
> ok
> 3) run the report from Report manager, click on "Properties" tab, choose
> "parameters":
> Has Default - leave checked on
> Default Value - (Query Based)
> Null - (none)
> Prompt User - leave checked on
> Prompt String - I cleared this textbox
> 4) run the report again:
> Reporting Services Error
> Default value or value provided for the report parameter 'MyParam' is not
> a
> valid value. (rsInvalidReportParameter) Get Online Help
> Refresh button on RM or the IE's same button don't change this error
> message
> 5) Properties -> Parameters -> Prompt String: type a former prompt string
> 6) run the report: it works ok, however the aforementioned parameter acts
> like it doesn't have Default Value anymore.
> What have I do to hide param properly?
> (MS RS sp1)
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> Thanks,
> Lucy.
>|||Yes. From query. Same to "default".
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Does it have list of available values?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I have troubles with hidden parameters. What am I doing:
> >
> > 1) define a report parameter based on a dataset's field (a dataset is
> > "Stored proc." type, the procedure returns the only string value -- if
it
> > is
> > important)
> > 2) Build & deploy a solution, run the report from Report manager -- it's
> > ok
> > 3) run the report from Report manager, click on "Properties" tab, choose
> > "parameters":
> > Has Default - leave checked on
> > Default Value - (Query Based)
> > Null - (none)
> > Prompt User - leave checked on
> > Prompt String - I cleared this textbox
> > 4) run the report again:
> >
> > Reporting Services Error
> > Default value or value provided for the report parameter 'MyParam' is
not
> > a
> > valid value. (rsInvalidReportParameter) Get Online Help
> >
> > Refresh button on RM or the IE's same button don't change this error
> > message
> >
> > 5) Properties -> Parameters -> Prompt String: type a former prompt
string
> > 6) run the report: it works ok, however the aforementioned parameter
acts
> > like it doesn't have Default Value anymore.
> >
> > What have I do to hide param properly?
> >
> > (MS RS sp1)
> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >
> > Thanks,
> > Lucy.
> >
> >
>|||To summarize, error rsInvalidReportParameter happened because same stored
procedure was used for available values and default value, and this stored
procedure returned unique result for every call.
Report Server was executing this stored procedure twice (1st time for
available values and 2nd time for default value) and was unable to match
default value with available value.
Solution is not to set available values.
Also because you just wanted to have read-only parameter, it is enough to
leave prompt blank, and Report Desinger would set parameter properties
during Deploy procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Yes. From query. Same to "default".
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
>> Does it have list of available values?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lusy Crown" <evesq@.uk2.net> wrote in message
>> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
>> > Hello,
>> >
>> > I have troubles with hidden parameters. What am I doing:
>> >
>> > 1) define a report parameter based on a dataset's field (a dataset is
>> > "Stored proc." type, the procedure returns the only string value -- if
> it
>> > is
>> > important)
>> > 2) Build & deploy a solution, run the report from Report manager --
>> > it's
>> > ok
>> > 3) run the report from Report manager, click on "Properties" tab,
>> > choose
>> > "parameters":
>> > Has Default - leave checked on
>> > Default Value - (Query Based)
>> > Null - (none)
>> > Prompt User - leave checked on
>> > Prompt String - I cleared this textbox
>> > 4) run the report again:
>> >
>> > Reporting Services Error
>> > Default value or value provided for the report parameter 'MyParam' is
> not
>> > a
>> > valid value. (rsInvalidReportParameter) Get Online Help
>> >
>> > Refresh button on RM or the IE's same button don't change this error
>> > message
>> >
>> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> string
>> > 6) run the report: it works ok, however the aforementioned parameter
> acts
>> > like it doesn't have Default Value anymore.
>> >
>> > What have I do to hide param properly?
>> >
>> > (MS RS sp1)
>> > Microsoft SQL Server Reporting Services Version 8.00.878.00
>> >
>> > Thanks,
>> > Lucy.
>> >
>> >
>>
>|||How do you not set available values...in report designer report params
dialog, my parameter has a name, 'tick', has the non-queried available values
radio button clicked and the list to the right is completely blank, as I want
it to be. This parameter is unique everytime a report is executed and cannot
be selected from a list of existing values...but I'm still getting the
following error when I call the web service render method...
+ System.SystemException {"The value provided for the report parameter
'tick' is not valid for its type. --> The value provided for the report
parameter 'tick' is not valid for its type. --> The value provided for the
report parameter 'tick' is not valid for its type."} System.SystemException
"Lev Semenets [MSFT]" wrote:
> To summarize, error rsInvalidReportParameter happened because same stored
> procedure was used for available values and default value, and this stored
> procedure returned unique result for every call.
> Report Server was executing this stored procedure twice (1st time for
> available values and 2nd time for default value) and was unable to match
> default value with available value.
> Solution is not to set available values.
> Also because you just wanted to have read-only parameter, it is enough to
> leave prompt blank, and Report Desinger would set parameter properties
> during Deploy procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Yes. From query. Same to "default".
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> >> Does it have list of available values?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Lusy Crown" <evesq@.uk2.net> wrote in message
> >> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> >> > Hello,
> >> >
> >> > I have troubles with hidden parameters. What am I doing:
> >> >
> >> > 1) define a report parameter based on a dataset's field (a dataset is
> >> > "Stored proc." type, the procedure returns the only string value -- if
> > it
> >> > is
> >> > important)
> >> > 2) Build & deploy a solution, run the report from Report manager --
> >> > it's
> >> > ok
> >> > 3) run the report from Report manager, click on "Properties" tab,
> >> > choose
> >> > "parameters":
> >> > Has Default - leave checked on
> >> > Default Value - (Query Based)
> >> > Null - (none)
> >> > Prompt User - leave checked on
> >> > Prompt String - I cleared this textbox
> >> > 4) run the report again:
> >> >
> >> > Reporting Services Error
> >> > Default value or value provided for the report parameter 'MyParam' is
> > not
> >> > a
> >> > valid value. (rsInvalidReportParameter) Get Online Help
> >> >
> >> > Refresh button on RM or the IE's same button don't change this error
> >> > message
> >> >
> >> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> > string
> >> > 6) run the report: it works ok, however the aforementioned parameter
> > acts
> >> > like it doesn't have Default Value anymore.
> >> >
> >> > What have I do to hide param properly?
> >> >
> >> > (MS RS sp1)
> >> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >> >
> >> > Thanks,
> >> > Lucy.
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Monday, February 13, 2012

[ URGENTlY URGENT ] PARAMETER LENGTH 1024

I got a parametrized report in Reporting Services.The problem is that i got Really large paramteres which are exceeding 1024 limit and if that probolem is not solved..please let me know a hotel where a waiter is Needed on good Pay and Respect. Big Smile

Try splitting into multiple parameters and then merging after the fact.
Or try using a table to store parameters based on user id & look up after the fact.
More information around application & parameter types would help.
URLs don't really support that much information, so you're better off saving the parameter to a database, then using an id field to retrieve the information in a stored procedure.|||Does this mean that there is a 1024 limit on the report parameters of RS?

[ URGENTlY URGENT ] PARAMETER LENGTH 1024

I got a parametrized report in Reporting Services.The problem is that i got Really large paramteres which are exceeding 1024 limit and if that probolem is not solved..please let me know a hotel where a waiter is Needed on good Pay and Respect. Big Smile

Try splitting into multiple parameters and then merging after the fact.
Or try using a table to store parameters based on user id & look up after the fact.
More information around application & parameter types would help.
URLs don't really support that much information, so you're better off saving the parameter to a database, then using an id field to retrieve the information in a stored procedure.|||Does this mean that there is a 1024 limit on the report parameters of RS?

Saturday, February 11, 2012

@terminate param for log shipping stored procs

SQL BOL says that the @.terminate parameter for sp_change_primary_role
defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
cases, this param "Specifies that all pending transactions be immediately
rolled back" and puts the db in single user mode while the stored proc runs.
I'm wondering why each of these has it different default value. I presume
that in both cases a value of 1 = true, meaning that it would do the
rollback. Am I correct in assuming this? In order to help me understand this
better, I hope someone could tell me why I would use the default values, and
in what cirucumstance I might not use the default values.
archuleta37,
Your installation of the SQL Server 2000 Books Online needs to be updated.
There were documentation problems with these procedures. See:
http://support.microsoft.com/kb/291160 Terminate Default
http://support.microsoft.com/kb/298093 Job Id
These KBs also have a link to a corrected version of the BOL:
http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> SQL BOL says that the @.terminate parameter for sp_change_primary_role
> defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> cases, this param "Specifies that all pending transactions be immediately
> rolled back" and puts the db in single user mode while the stored proc
> runs.
> I'm wondering why each of these has it different default value. I presume
> that in both cases a value of 1 = true, meaning that it would do the
> rollback. Am I correct in assuming this? In order to help me understand
> this
> better, I hope someone could tell me why I would use the default values,
> and
> in what cirucumstance I might not use the default values.
|||Thank you Russell,
This helps clarify a lot. I found that the link to the updated BOL you sent
is no longer valid, but I was easily able to search and find it at
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
The circumstance I'm testing assumes that the primary is completely
unavailable, yet when running sp_change_secondary_role there may be some
transaction log backups (.trn) files that are still loading. So I'm thinking
I should probably set the @.terminate param to 0. Would you agree with this
assessment or do you think I missing something in my reasoning?
"Russell Fields" wrote:

> archuleta37,
> Your installation of the SQL Server 2000 Books Online needs to be updated.
> There were documentation problems with these procedures. See:
> http://support.microsoft.com/kb/291160 Terminate Default
> http://support.microsoft.com/kb/298093 Job Id
> These KBs also have a link to a corrected version of the BOL:
> http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
> RLF
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
>
>
|||archuleta37,
I am afraid that I am not log shipping handy, so perhaps someone who does
this will have a quick answer. However, in the SQL Server 2000 Books Online
is a topic titled "How to set up and perform a log shipping role change
(Transact-SQL)" that seems to answer your question.
Also, a FAQ is found at: http://support.microsoft.com/kb/314515/en-us
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:509BEAF1-1B5D-4609-B92C-DF041A215B01@.microsoft.com...[vbcol=seagreen]
> Thank you Russell,
> This helps clarify a lot. I found that the link to the updated BOL you
> sent
> is no longer valid, but I was easily able to search and find it at
> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
> The circumstance I'm testing assumes that the primary is completely
> unavailable, yet when running sp_change_secondary_role there may be some
> transaction log backups (.trn) files that are still loading. So I'm
> thinking
> I should probably set the @.terminate param to 0. Would you agree with this
> assessment or do you think I missing something in my reasoning?
>
> "Russell Fields" wrote:

@terminate param for log shipping stored procs

SQL BOL says that the @.terminate parameter for sp_change_primary_role
defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
cases, this param "Specifies that all pending transactions be immediately
rolled back" and puts the db in single user mode while the stored proc runs.
I'm wondering why each of these has it different default value. I presume
that in both cases a value of 1 = true, meaning that it would do the
rollback. Am I correct in assuming this? In order to help me understand this
better, I hope someone could tell me why I would use the default values, and
in what cirucumstance I might not use the default values.archuleta37,
Your installation of the SQL Server 2000 Books Online needs to be updated.
There were documentation problems with these procedures. See:
http://support.microsoft.com/kb/291160 Terminate Default
http://support.microsoft.com/kb/298093 Job Id
These KBs also have a link to a corrected version of the BOL:
http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> SQL BOL says that the @.terminate parameter for sp_change_primary_role
> defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> cases, this param "Specifies that all pending transactions be immediately
> rolled back" and puts the db in single user mode while the stored proc
> runs.
> I'm wondering why each of these has it different default value. I presume
> that in both cases a value of 1 = true, meaning that it would do the
> rollback. Am I correct in assuming this? In order to help me understand
> this
> better, I hope someone could tell me why I would use the default values,
> and
> in what cirucumstance I might not use the default values.|||Thank you Russell,
This helps clarify a lot. I found that the link to the updated BOL you sent
is no longer valid, but I was easily able to search and find it at
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
The circumstance I'm testing assumes that the primary is completely
unavailable, yet when running sp_change_secondary_role there may be some
transaction log backups (.trn) files that are still loading. So I'm thinking
I should probably set the @.terminate param to 0. Would you agree with this
assessment or do you think I missing something in my reasoning?
"Russell Fields" wrote:
> archuleta37,
> Your installation of the SQL Server 2000 Books Online needs to be updated.
> There were documentation problems with these procedures. See:
> http://support.microsoft.com/kb/291160 Terminate Default
> http://support.microsoft.com/kb/298093 Job Id
> These KBs also have a link to a corrected version of the BOL:
> http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
> RLF
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> > SQL BOL says that the @.terminate parameter for sp_change_primary_role
> > defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> > cases, this param "Specifies that all pending transactions be immediately
> > rolled back" and puts the db in single user mode while the stored proc
> > runs.
> >
> > I'm wondering why each of these has it different default value. I presume
> > that in both cases a value of 1 = true, meaning that it would do the
> > rollback. Am I correct in assuming this? In order to help me understand
> > this
> > better, I hope someone could tell me why I would use the default values,
> > and
> > in what cirucumstance I might not use the default values.
>
>|||archuleta37,
I am afraid that I am not log shipping handy, so perhaps someone who does
this will have a quick answer. However, in the SQL Server 2000 Books Online
is a topic titled "How to set up and perform a log shipping role change
(Transact-SQL)" that seems to answer your question.
Also, a FAQ is found at: http://support.microsoft.com/kb/314515/en-us
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:509BEAF1-1B5D-4609-B92C-DF041A215B01@.microsoft.com...
> Thank you Russell,
> This helps clarify a lot. I found that the link to the updated BOL you
> sent
> is no longer valid, but I was easily able to search and find it at
> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
> The circumstance I'm testing assumes that the primary is completely
> unavailable, yet when running sp_change_secondary_role there may be some
> transaction log backups (.trn) files that are still loading. So I'm
> thinking
> I should probably set the @.terminate param to 0. Would you agree with this
> assessment or do you think I missing something in my reasoning?
>
> "Russell Fields" wrote:
>> archuleta37,
>> Your installation of the SQL Server 2000 Books Online needs to be
>> updated.
>> There were documentation problems with these procedures. See:
>> http://support.microsoft.com/kb/291160 Terminate Default
>> http://support.microsoft.com/kb/298093 Job Id
>> These KBs also have a link to a corrected version of the BOL:
>> http://www.microsoft.com/downloads/release.asp?ReleaseID=31343
>> RLF
>> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
>> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
>> > SQL BOL says that the @.terminate parameter for sp_change_primary_role
>> > defaults to 0 while it defaults to 1 in sp_change_secondary_role. In
>> > both
>> > cases, this param "Specifies that all pending transactions be
>> > immediately
>> > rolled back" and puts the db in single user mode while the stored proc
>> > runs.
>> >
>> > I'm wondering why each of these has it different default value. I
>> > presume
>> > that in both cases a value of 1 = true, meaning that it would do the
>> > rollback. Am I correct in assuming this? In order to help me understand
>> > this
>> > better, I hope someone could tell me why I would use the default
>> > values,
>> > and
>> > in what cirucumstance I might not use the default values.
>>

@terminate param for log shipping stored procs

SQL BOL says that the @.terminate parameter for sp_change_primary_role
defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
cases, this param "Specifies that all pending transactions be immediately
rolled back" and puts the db in single user mode while the stored proc runs.
I'm wondering why each of these has it different default value. I presume
that in both cases a value of 1 = true, meaning that it would do the
rollback. Am I correct in assuming this? In order to help me understand this
better, I hope someone could tell me why I would use the default values, and
in what cirucumstance I might not use the default values.archuleta37,
Your installation of the SQL Server 2000 Books Online needs to be updated.
There were documentation problems with these procedures. See:
http://support.microsoft.com/kb/291160 Terminate Default
http://support.microsoft.com/kb/298093 Job Id
These KBs also have a link to a corrected version of the BOL:
http://www.microsoft.com/downloads/...ReleaseID=31343
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
> SQL BOL says that the @.terminate parameter for sp_change_primary_role
> defaults to 0 while it defaults to 1 in sp_change_secondary_role. In both
> cases, this param "Specifies that all pending transactions be immediately
> rolled back" and puts the db in single user mode while the stored proc
> runs.
> I'm wondering why each of these has it different default value. I presume
> that in both cases a value of 1 = true, meaning that it would do the
> rollback. Am I correct in assuming this? In order to help me understand
> this
> better, I hope someone could tell me why I would use the default values,
> and
> in what cirucumstance I might not use the default values.|||Thank you Russell,
This helps clarify a lot. I found that the link to the updated BOL you sent
is no longer valid, but I was easily able to search and find it at
http://www.microsoft.com/downloads/...&DisplayLang=en
The circumstance I'm testing assumes that the primary is completely
unavailable, yet when running sp_change_secondary_role there may be some
transaction log backups (.trn) files that are still loading. So I'm thinking
I should probably set the @.terminate param to 0. Would you agree with this
assessment or do you think I missing something in my reasoning?
"Russell Fields" wrote:

> archuleta37,
> Your installation of the SQL Server 2000 Books Online needs to be updated.
> There were documentation problems with these procedures. See:
> http://support.microsoft.com/kb/291160 Terminate Default
> http://support.microsoft.com/kb/298093 Job Id
> These KBs also have a link to a corrected version of the BOL:
> http://www.microsoft.com/downloads/...ReleaseID=31343
> RLF
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:D0E72DDC-4147-4D25-AAE3-D83921D9A011@.microsoft.com...
>
>|||archuleta37,
I am afraid that I am not log shipping handy, so perhaps someone who does
this will have a quick answer. However, in the SQL Server 2000 Books Online
is a topic titled "How to set up and perform a log shipping role change
(Transact-SQL)" that seems to answer your question.
Also, a FAQ is found at: http://support.microsoft.com/kb/314515/en-us
RLF
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:509BEAF1-1B5D-4609-B92C-DF041A215B01@.microsoft.com...[vbcol=seagreen]
> Thank you Russell,
> This helps clarify a lot. I found that the link to the updated BOL you
> sent
> is no longer valid, but I was easily able to search and find it at
> http://www.microsoft.com/downloads/...&DisplayLang=en
> The circumstance I'm testing assumes that the primary is completely
> unavailable, yet when running sp_change_secondary_role there may be some
> transaction log backups (.trn) files that are still loading. So I'm
> thinking
> I should probably set the @.terminate param to 0. Would you agree with this
> assessment or do you think I missing something in my reasoning?
>
> "Russell Fields" wrote:
>

@schema_change_script usage

When does the script specified in the @.schema_change_script parameter for
the sp_repladdcolumn and sp_repldropcolumn get fired ? Is it after the
adding and dropping of the column on all ends or before ?
Thanks
after.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ObWssO%23VFHA.2796@.TK2MSFTNGP09.phx.gbl...
> When does the script specified in the @.schema_change_script parameter for
> the sp_repladdcolumn and sp_repldropcolumn get fired ? Is it after the
> adding and dropping of the column on all ends or before ?
> Thanks
>