mapping XML data to variable

I can’t figure out how to map xml data stored in a table to a variable in integration service.

For example:
I would like to use a “for each loop container” to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like “variable mapping number X to variable XXX can’t apply”.

Any help?

This is a supported scenario. Ensure that:

The column is actually being loaded into the record set: Check the column mappings in the recordset dest The value being mapped to the variable is less than 4000 characters long: select max(datalength(xmlCol)) from XmlTable

Mapping Source and Destination columns

can somebody show an example of how to map source and destination columns when uploading a file to sql server?

Also, please send me the mapping when i want to map source to different destination columns.

I think you have to to be a bit more specific to get answers that will help you.

many to many dimension

Hi all,

Is there a way to create a dimension property based on fact table used to connect a dimension to another measure group. I found a example where such an attibute is used for a measure expression(http://msdn2.microsoft.com/en-us/library/ms345139.aspx) but I really need a property that I could display to the end user.

thx

Fred

Hi again...just to be a bit more clear on what I'm trying to do...if you look at the example I mentioned....how could the value of the 'ownership' be displayed instead of used in a calculation?

Fred

|||Well, in this example, the user could browse the value of the 'ownership' measure in the intermediate measure group, for a given selection of Store and Organization. If that's not adequate, you could define a fact dimension for the intermediate measure group fact table, and add 'ownership' as an attribute (with the appropriate type).|||

Thanks Deepak! The fact dimension will do the trick

Fred

Manually kick off a subscription

Using Report Manger, is it possible to manually kick off a subscription?
For example, if I have 100 scheduled reports and for some reason they do not
go, or need to be resent (for example, in my case a replication error) can
those reports be manually kicked off? I certainly don't want to rerun each
report and manually email all of these reports.
ThanksNo, through report manager you cannot manually kick off a subscription. The
workaround isn't pretty:
You can use entreprise manager to kick off a particular schedule. The only
thing you need is to get the schedule ID that the subscription is tied to.
One problem with this approach is that you'll kick off everything that is
attached to the schedule.
If you're using a shared schedule. You can find out the schedule ID by
calling the GetSubscriptionProperties method. It will come back in the
match data.
If you're using a subscription specific schedule, the easiest way is to
resave the subscription. This will create a new job in SQL Agent. So if
there aren't other users creating schedules at the same time, you'll be able
to look at the list of jobs at the same time, you'll see an entry in the
list of jobs which has never executed. Odds are this is the schedule you're
looking for.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"troark" <t.roark.n0spam@.excite.com> wrote in message
news:%23GL7nZbbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Using Report Manger, is it possible to manually kick off a subscription?
> For example, if I have 100 scheduled reports and for some reason they do
> not
> go, or need to be resent (for example, in my case a replication error) can
> those reports be manually kicked off? I certainly don't want to rerun
> each
> report and manually email all of these reports.
> Thanks
>|||I figured out part of this, but not all.
It looks like it's a better idea to use Shared Schedules for what I'm doing.
If I need to rerun the whole schedule, then I just modify the execution time
and change it back after it completes.
However, what if I have 100 reports and 50 need to be re-ran? Looks like
there is not a way to do this. Is that right? If not, it might be a good
idea to look at this for future releases. Allow us to choose individual
reports in a Shared Schedule and reprocess them.
Thanks
"troark" <t.roark.n0spam@.excite.com> wrote in message
news:#GL7nZbbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Using Report Manger, is it possible to manually kick off a subscription?
> For example, if I have 100 scheduled reports and for some reason they do
not
> go, or need to be resent (for example, in my case a replication error) can
> those reports be manually kicked off? I certainly don't want to rerun
each
> report and manually email all of these reports.
> Thanks
>|||Thanks for the response. I was hoping for a different answer. I will soon
have over 300 scheduled reports. It is very likely that I will have to
re-run reports for one reason or another. I was hoping that I would be able
to use Report Manager without having to write something myself. Hopefully
this feature will be discussed for future releases.
Thanks again.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:eI8t8fcbEHA.3804@.TK2MSFTNGP10.phx.gbl...
> No, through report manager you cannot manually kick off a subscription.
The
> workaround isn't pretty:
> You can use entreprise manager to kick off a particular schedule. The
only
> thing you need is to get the schedule ID that the subscription is tied to.
> One problem with this approach is that you'll kick off everything that is
> attached to the schedule.
> If you're using a shared schedule. You can find out the schedule ID by
> calling the GetSubscriptionProperties method. It will come back in the
> match data.
> If you're using a subscription specific schedule, the easiest way is to
> resave the subscription. This will create a new job in SQL Agent. So if
> there aren't other users creating schedules at the same time, you'll be
able
> to look at the list of jobs at the same time, you'll see an entry in the
> list of jobs which has never executed. Odds are this is the schedule
you're
> looking for.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "troark" <t.roark.n0spam@.excite.com> wrote in message
> news:%23GL7nZbbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> > Using Report Manger, is it possible to manually kick off a subscription?
> > For example, if I have 100 scheduled reports and for some reason they do
> > not
> > go, or need to be resent (for example, in my case a replication error)
can
> > those reports be manually kicked off? I certainly don't want to rerun
> > each
> > report and manually email all of these reports.
> >
> > Thanks
> >
> >
>|||Yes, we are considering such a feature for a future release.
The way to make this work is to create a shared schedule that never fires
(occurs once where that occurance is in the past). Then hook up your
subscription to this schedule.
The shared schedule will match a job in SQL Agent.
To run a report on demand, use the SQL Agent APIs to trigger the job.
I know, this solution sucks...
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"troark" <t.roark.n0spam@.excite.com> wrote in message
news:Or0ORjcbEHA.212@.TK2MSFTNGP12.phx.gbl...
>I figured out part of this, but not all.
> It looks like it's a better idea to use Shared Schedules for what I'm
> doing.
> If I need to rerun the whole schedule, then I just modify the execution
> time
> and change it back after it completes.
> However, what if I have 100 reports and 50 need to be re-ran? Looks like
> there is not a way to do this. Is that right? If not, it might be a good
> idea to look at this for future releases. Allow us to choose individual
> reports in a Shared Schedule and reprocess them.
> Thanks
>
> "troark" <t.roark.n0spam@.excite.com> wrote in message
> news:#GL7nZbbEHA.1732@.TK2MSFTNGP09.phx.gbl...
>> Using Report Manger, is it possible to manually kick off a subscription?
>> For example, if I have 100 scheduled reports and for some reason they do
> not
>> go, or need to be resent (for example, in my case a replication error)
>> can
>> those reports be manually kicked off? I certainly don't want to rerun
> each
>> report and manually email all of these reports.
>> Thanks
>>
>

manipulation with numbers in SQL

Hi folks,

I am trying to divide a number by another number.. for example,

'-5037542 / 143149696'

The answer should be -0.03519.

Inspite of using round,convert and numeric functions i am unable to get the answer as above. It calculates wrongly..

I would appreciate if anyone could help.

TIA,
Nishaint / int = int, convert either number to a float and you will get your answer
select -5037542 / cast(143149696 as float) as 'Ans'

Ans
----------------
-0.0351907278936869|||Thanks a lot Paul Young!!!

-Nisha

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated
"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA
|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005
.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/200
5.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:
> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
> >Hi
> >
> >I have a field called paid to date and need to calculate the next date with
> >the same day of the month from getdate(). For example, paid to date of
> >13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> > However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> >
> >Any help in achieving this would be greatly appreciated
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulate Page-Numbers

Hi,

I need to manipulate the page-numbers, I've a dataset with for example 8 Records, where each record has its own page (page break at end) but some Records have 2Pages. So I want that every "first" page of a record gets page-number 1 and for those reports that need two pages the next page should have page-number 2. So in a PDF the page numbers look be like this: 1,1,1,2,1,1,2,3,1,1,1,2...

I tried a custom assembly with a static variable m_page on it which is resettet to 1 in a textfield at the recordbegin (=MyLib.MyClass.resetPage()) and is shown and incrementet in each page footer (=MyLib.MyClass.nextPage()). But when I print that to PDF it seems that the page footers are alltogether generated at the end, so I get numbers from for example 20 to 30 (my report has 10pages).

Is there any possibility? In Access this was quite easy ;(

Sorry bothering you, I should have used the search with the right keywords ;)

The solution is:

http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx

Managing large number of objects (table,sp,view)

Hi,

Our database has very large number of objects. We have a naming convension by modules, subprojects etc. But for example when we need to open a specific table it still takes time to find it. If we could create custom folders under table folder or stored procedure folder it will be easier to find an object. We could create sub folders by module, subproject and classify our objects with these folders. Will the next version SQL Server 2008 support this kind of functionality?

I think to a certian limit Schema can solve your problem. You can post your suggestion in Microsoft Connect...

Madhu

|||

I've worked with databases containing hundreds of tables, and thousand of code objects. Good schema design, and enforcing rigorous naming conventions have always worked to expediently locate the sought object.

As Madhu indicated, you can 'group' by schema. Explore the AdventureWorks database for topical seperation examples.

If you think that having some form of 'sub-folder' would help with organization, please offer the suggestion to the SQL Deveopment team at:

http://connect.microsoft.com/sqlserver

(I would search first. If someone else has offered the idea, add your 'vote' to the suggestion. It is a 'popularity' contest.

|||

Thanks for reply, I have posted this to Microsoft Connect as suggestion.

|||

if we grouped tables by schema, shall we modify programs code files developed over the database?

|||Yes, you will have to add the schema to the object name every place the object is referenced.

|||

Microsoft connect suggestion for this issue is here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289521

Managing Insert with Identity

Hi,
I am new at stored procedures.
I am running SQL Server 2005 standard on XP Pro sp2.
I have a database that has about 100 tables. For example I have a table
called 'People' with a primary key called 'PeopleID', which is an int and
also of column type 'Identity'. This 'People' table also has a
'PeopleAttributesID' column, also of int nature, and relates to a table name
d
'PeopleAttributes' of which the primary key and Identity column is name
'PeopleAttributesID'. PeopleAttributes table also has a column named
'PeopleInterfaceListID' of int that also relates to a table called
PeopleInterfaceList that has a primary key, int, identity, called
PeopleInterfaceListID.
You get the idea. :-)
My question is how to create a stored procedure for inserting new people in
the 'People' table. My issues is dealing with the identity columns of the
'People' table and it's related tables.
How do I add a row into a table that has an identity primary key and has
foreign keys into tables that also have identity primary keys. Can you give
me an example of how this should be done in your opinion?
Thank you very, very much :)
Antoine Dubuc
MSN Messenger : banquo_ws@.hotmail.com
514-761-1832Antoine wrote:
> Hi,
> I am new at stored procedures.
> I am running SQL Server 2005 standard on XP Pro sp2.
> I have a database that has about 100 tables. For example I have a
> table called 'People' with a primary key called 'PeopleID', which is
> an int and also of column type 'Identity'. This 'People' table also
> has a 'PeopleAttributesID' column, also of int nature, and relates to
> a table named 'PeopleAttributes' of which the primary key and
> Identity column is name 'PeopleAttributesID'. PeopleAttributes table
> also has a column named 'PeopleInterfaceListID' of int that also
> relates to a table called PeopleInterfaceList that has a primary key,
> int, identity, called PeopleInterfaceListID.
> You get the idea. :-)
> My question is how to create a stored procedure for inserting new
> people in the 'People' table. My issues is dealing with the identity
> columns of the 'People' table and it's related tables.
> How do I add a row into a table that has an identity primary key and
> has foreign keys into tables that also have identity primary keys.
> Can you give me an example of how this should be done in your opinion?
> Thank you very, very much :)
> Antoine Dubuc
> MSN Messenger : banquo_ws@.hotmail.com
> 514-761-1832
Youinsert by leaving off the identity column. Once the insert is complete,
you can get the new value using the SCOPE_IDENTITY() function. if you need
to insert FK values, you need to pass them into the stored procedure and use
them in the insert. You'll need to get those values from your application
first.
David Gugick
Quest Software|||Hi David,
When I try to execute this, I get the cannot leave null for this column erro
r.
DI have to manually insert it using something like:
DECLARE PeopleID INT
PeopleID = SCOPE_IDENTITY
...
Exactly how do you do this?
thank you,
Antoine
"David Gugick" wrote:

> Antoine wrote:
> Youinsert by leaving off the identity column. Once the insert is complete,
> you can get the new value using the SCOPE_IDENTITY() function. if you need
> to insert FK values, you need to pass them into the stored procedure and u
se
> them in the insert. You'll need to get those values from your application
> first.
> --
> David Gugick
> Quest Software
>
>|||> When I try to execute this, I get the cannot leave null for this column
> error.
> DI have to manually insert it using something like:
> DECLARE PeopleID INT
> PeopleID = SCOPE_IDENTITY
Did you mean:
DECLARE @.PeopleID INT
... INSERT statement here
SET @.PeopleID = SCOPE_IDENTITY()
?|||>> have a database that has about 100 tables. For example I have a table ca
lled 'People' with a primary key called 'PeopleID', which is an int and also
of column type 'Identity'. This 'People' table also has a 'PeopleAttribute
sID' column, also of int na
ture, and relates to a table named 'PeopleAttributes' of which the primary k
ey and Identity column is named 'PeopleAttributesID'. PeopleAttributes table
also has a column named 'PeopleInterfaceListID' of int that also relates to
a table called PeopleInter
faceList that has a primary key, int, identity, called PeopleInterfaceListID
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
However, these data element names and the improper use of IDENTITY as a
key imply that you have never written an RDBMS before. IDENTITY is not
a key by definition and you are mixing data and metadata. It looks
like the typhical newbie disaster with IDENTITY columns used as pointer
chains to mimic a 1970's IDMS database. Please get help instead of
newsgroup kludges.|||In order for a column with the IDENTITY 'property' to become a key then you
need only make it such using a CONSTRAINT, either PRIMARY KEY where no
natural key exists or UNIQUE if you are making it an artificial or surrogate
key.
Get a grip and be more constructive, the poster said his level of experience
was beginner so be polite and don't rant your rude rubbish.
There are many objects in the world that do not have a natural key, my
favourite is the message board example.
It's quite concievable that the author wants to create an auto generated
number and use that as a key, afterall, its becoming very dodgy to say the
least in the US using social security numbers and capturing that info on a
website, well - would you honestly give over your national insurance number
(uk equiv) to a company - nope.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132258326.365651.286720@.g14g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> However, these data element names and the improper use of IDENTITY as a
> key imply that you have never written an RDBMS before. IDENTITY is not
> a key by definition and you are mixing data and metadata. It looks
> like the typhical newbie disaster with IDENTITY columns used as pointer
> chains to mimic a 1970's IDMS database. Please get help instead of
> newsgroup kludges.
>

Managing Distributed Transactions with ADO.NET 2.0 using TransactionScope gives error mess

Hi,

I am working on vs2005 with sql server 2000. I have used TransactionScope class.

Example Reference:

http://www.c-sharpcorner.com/UploadFile/mosessaur/TransactionScope04142006103850AM/TransactionScope.aspx

The code is given below.

using System.Transactions;

protected void Page_Load(object sender, EventArgs e)
{

System.Transactions.TransactionOptions transOption = new System.Transactions.TransactionOptions();
transOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
transOption.Timeout = new TimeSpan(0, 2, 0);

using (System.Transactions.TransactionScope tranScope = new System.Transactions.TransactionScope(TransactionScopeOption.Required,transOption))
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["nwConnString"].ConnectionString))
{
int i;
con.Open();
SqlCommand cmd = new SqlCommand("update products set unitsinstock=100 where productid=1", con);
i = cmd.ExecuteNonQuery();
if (i > 0)
{
using (SqlConnection conInner = new SqlConnection(ConfigurationManager.ConnectionStrings["pubsConnString"].ConnectionString))
{
conInner.Open();
SqlCommand cmdInner = new SqlCommand("update Salary set sal=5000 where eno=1", conInner);
i = cmdInner.ExecuteNonQuery();
if (i > 0)
{
tranScope.Complete(); // this statement commits the executed query.
}
}
}
}
// Dispose TransactionScope object, to commit or rollback transaction.
}

}

It gives error like

"The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)"

The database I have used is northwind database and pubs database which is by default in sql server 2000.

So, Kindly let me know how to proceed further.

Thanks in advance,

Arun.

Hi,

From your description, it seems that you met the "The partner transaction manager has disabled" error when you want to run the distributed transaction in your project, right?

Generally, the cause of the issue is that you didn't set the transaction service properly. You may following the steps below:

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

Quoted from community members of MSDN:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

Thanks.

Managing dev, test, and pro environment

I would like to find some best practice documentation on how to manage a
development, test, and production configuration. For example, I have an SRS
development server that runs on a development SQL Server. Developers write
the SRS reports. Then it comes time to deploy the reports to user testing.
I have a SRS test server that may run on a test SQL server. When it comes
time to deploy the reports into production, I have an SRS production server.
I want to find out how to best accomplish this.
ThanksThat sounds like a good plan. The next step would be to identify the actual
way you push the RDL files around. Depending on whether you are using forms
auth or windows auth -- as well as how you handle configuration management
in your company -- the best solution may vary.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in message
news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
>I would like to find some best practice documentation on how to manage a
> development, test, and production configuration. For example, I have an
> SRS
> development server that runs on a development SQL Server. Developers
> write
> the SRS reports. Then it comes time to deploy the reports to user
> testing.
> I have a SRS test server that may run on a test SQL server. When it comes
> time to deploy the reports into production, I have an SRS production
> server.
> I want to find out how to best accomplish this.
> Thanks
>|||You hit the nail on the head. How do you push the RDLs around? The only
thought I have on this now is the developer or the configuration management
person would have to open the report in VS.NET and publish it to a different
SRS server. Sounds messy. Maybe a better approach would be for a function
in Report Manager to push RDLs around. But then, I have to wonder about
versions of the RDL.
Surely someone has thought about this. Does everyone on the planet code and
test on a production server?
"Jeff A. Stucker" wrote:
> That sounds like a good plan. The next step would be to identify the actual
> way you push the RDL files around. Depending on whether you are using forms
> auth or windows auth -- as well as how you handle configuration management
> in your company -- the best solution may vary.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in message
> news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
> >I would like to find some best practice documentation on how to manage a
> > development, test, and production configuration. For example, I have an
> > SRS
> > development server that runs on a development SQL Server. Developers
> > write
> > the SRS reports. Then it comes time to deploy the reports to user
> > testing.
> > I have a SRS test server that may run on a test SQL server. When it comes
> > time to deploy the reports into production, I have an SRS production
> > server.
> > I want to find out how to best accomplish this.
> >
> > Thanks
> >
>
>|||You can create a solution using rs.exe, that is, scripting the deployment.
Take a look here (default installation location):
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Scripts
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
news:A944148D-6829-471D-ADA7-61C69AEB5C00@.microsoft.com...
> You hit the nail on the head. How do you push the RDLs around? The only
> thought I have on this now is the developer or the configuration
> management
> person would have to open the report in VS.NET and publish it to a
> different
> SRS server. Sounds messy. Maybe a better approach would be for a
> function
> in Report Manager to push RDLs around. But then, I have to wonder about
> versions of the RDL.
> Surely someone has thought about this. Does everyone on the planet code
> and
> test on a production server?
> "Jeff A. Stucker" wrote:
>> That sounds like a good plan. The next step would be to identify the
>> actual
>> way you push the RDL files around. Depending on whether you are using
>> forms
>> auth or windows auth -- as well as how you handle configuration
>> management
>> in your company -- the best solution may vary.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in message
>> news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
>> >I would like to find some best practice documentation on how to manage a
>> > development, test, and production configuration. For example, I have
>> > an
>> > SRS
>> > development server that runs on a development SQL Server. Developers
>> > write
>> > the SRS reports. Then it comes time to deploy the reports to user
>> > testing.
>> > I have a SRS test server that may run on a test SQL server. When it
>> > comes
>> > time to deploy the reports into production, I have an SRS production
>> > server.
>> > I want to find out how to best accomplish this.
>> >
>> > Thanks
>> >
>>|||This was just posted today. You might want to take a look at it.
>>>>>>>>>>>>>
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:<#zNZyc0HFHA.2976@.TK2MSFTNGP15.phx.gbl>...
Download Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
Reporting Services Scripter is a .NET Windows Forms application that enables
scripting of all Microsoft SQL Server Reporting Services catalog items to
aid in transferring them from one server to another. It can also be used to
easily move items on mass from one Reporting Services folder to another on
the same server. Depending on the scripting options chosen, Reporting
Services Scripter can also transfer all catalog item properties such as
Descriptions, History options, Execution options (including report specific
and shared schedules) and server side report parameters.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
>>>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
news:A944148D-6829-471D-ADA7-61C69AEB5C00@.microsoft.com...
> You hit the nail on the head. How do you push the RDLs around? The only
> thought I have on this now is the developer or the configuration
management
> person would have to open the report in VS.NET and publish it to a
different
> SRS server. Sounds messy. Maybe a better approach would be for a
function
> in Report Manager to push RDLs around. But then, I have to wonder about
> versions of the RDL.
> Surely someone has thought about this. Does everyone on the planet code
and
> test on a production server?
> "Jeff A. Stucker" wrote:
> > That sounds like a good plan. The next step would be to identify the
actual
> > way you push the RDL files around. Depending on whether you are using
forms
> > auth or windows auth -- as well as how you handle configuration
management
> > in your company -- the best solution may vary.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in message
> > news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
> > >I would like to find some best practice documentation on how to manage
a
> > > development, test, and production configuration. For example, I have
an
> > > SRS
> > > development server that runs on a development SQL Server. Developers
> > > write
> > > the SRS reports. Then it comes time to deploy the reports to user
> > > testing.
> > > I have a SRS test server that may run on a test SQL server. When it
comes
> > > time to deploy the reports into production, I have an SRS production
> > > server.
> > > I want to find out how to best accomplish this.
> > >
> > > Thanks
> > >
> >
> >
> >|||Very good Bruce and Jeff. This is what I needed to know. We have yet to
move to SRS, but before we do, I want some questions like this answered. I
assume you have used this tool and it works as advertised?
"Bruce L-C [MVP]" wrote:
> This was just posted today. You might want to take a look at it.
> >>>>>>>>>>>>>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:<#zNZyc0HFHA.2976@.TK2MSFTNGP15.phx.gbl>...
> Download Reporting Services Scripter
> http://www.sqldbatips.com/showarticle.asp?ID=62
>
> Reporting Services Scripter is a .NET Windows Forms application that enables
> scripting of all Microsoft SQL Server Reporting Services catalog items to
> aid in transferring them from one server to another. It can also be used to
> easily move items on mass from one Reporting Services folder to another on
> the same server. Depending on the scripting options chosen, Reporting
> Services Scripter can also transfer all catalog item properties such as
> Descriptions, History options, Execution options (including report specific
> and shared schedules) and server side report parameters.
>
> --
> HTH
>
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> >>>>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> news:A944148D-6829-471D-ADA7-61C69AEB5C00@.microsoft.com...
> > You hit the nail on the head. How do you push the RDLs around? The only
> > thought I have on this now is the developer or the configuration
> management
> > person would have to open the report in VS.NET and publish it to a
> different
> > SRS server. Sounds messy. Maybe a better approach would be for a
> function
> > in Report Manager to push RDLs around. But then, I have to wonder about
> > versions of the RDL.
> >
> > Surely someone has thought about this. Does everyone on the planet code
> and
> > test on a production server?
> >
> > "Jeff A. Stucker" wrote:
> >
> > > That sounds like a good plan. The next step would be to identify the
> actual
> > > way you push the RDL files around. Depending on whether you are using
> forms
> > > auth or windows auth -- as well as how you handle configuration
> management
> > > in your company -- the best solution may vary.
> > >
> > > --
> > > Cheers,
> > >
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in message
> > > news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
> > > >I would like to find some best practice documentation on how to manage
> a
> > > > development, test, and production configuration. For example, I have
> an
> > > > SRS
> > > > development server that runs on a development SQL Server. Developers
> > > > write
> > > > the SRS reports. Then it comes time to deploy the reports to user
> > > > testing.
> > > > I have a SRS test server that may run on a test SQL server. When it
> comes
> > > > time to deploy the reports into production, I have an SRS production
> > > > server.
> > > > I want to find out how to best accomplish this.
> > > >
> > > > Thanks
> > > >
> > >
> > >
> > >
>
>|||I have not used it. It is a tool development by a SQL Server MVP. He just
finished it so I don't know what it is like. Remember you can create your
own scripts to so you have lots of options. I would kick the tires on it
some before I depend on it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
news:E2BB9DD3-011C-4648-B9E1-857950FC7227@.microsoft.com...
> Very good Bruce and Jeff. This is what I needed to know. We have yet to
> move to SRS, but before we do, I want some questions like this answered.
I
> assume you have used this tool and it works as advertised?
> "Bruce L-C [MVP]" wrote:
> > This was just posted today. You might want to take a look at it.
> > >>>>>>>>>>>>>
> > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > news:<#zNZyc0HFHA.2976@.TK2MSFTNGP15.phx.gbl>...
> >
> > Download Reporting Services Scripter
> >
> > http://www.sqldbatips.com/showarticle.asp?ID=62
> >
> >
> > Reporting Services Scripter is a .NET Windows Forms application that
enables
> > scripting of all Microsoft SQL Server Reporting Services catalog items
to
> > aid in transferring them from one server to another. It can also be used
to
> > easily move items on mass from one Reporting Services folder to another
on
> > the same server. Depending on the scripting options chosen, Reporting
> > Services Scripter can also transfer all catalog item properties such as
> > Descriptions, History options, Execution options (including report
specific
> > and shared schedules) and server side report parameters.
> >
> >
> > --
> >
> > HTH
> >
> >
> > Jasper Smith (SQL Server MVP)
> >
> > http://www.sqldbatips.com
> >
> > I support PASS - the definitive, global
> >
> > community for SQL Server professionals -
> >
> > http://www.sqlpass.org
> >
> > >>>>>>>>>>>>>>>>
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> > news:A944148D-6829-471D-ADA7-61C69AEB5C00@.microsoft.com...
> > > You hit the nail on the head. How do you push the RDLs around? The
only
> > > thought I have on this now is the developer or the configuration
> > management
> > > person would have to open the report in VS.NET and publish it to a
> > different
> > > SRS server. Sounds messy. Maybe a better approach would be for a
> > function
> > > in Report Manager to push RDLs around. But then, I have to wonder
about
> > > versions of the RDL.
> > >
> > > Surely someone has thought about this. Does everyone on the planet
code
> > and
> > > test on a production server?
> > >
> > > "Jeff A. Stucker" wrote:
> > >
> > > > That sounds like a good plan. The next step would be to identify
the
> > actual
> > > > way you push the RDL files around. Depending on whether you are
using
> > forms
> > > > auth or windows auth -- as well as how you handle configuration
> > management
> > > > in your company -- the best solution may vary.
> > > >
> > > > --
> > > > Cheers,
> > > >
> > > > '(' Jeff A. Stucker
> > > > \
> > > >
> > > > Business Intelligence
> > > > www.criadvantage.com
> > > > ---
> > > > "Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in message
> > > > news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
> > > > >I would like to find some best practice documentation on how to
manage
> > a
> > > > > development, test, and production configuration. For example, I
have
> > an
> > > > > SRS
> > > > > development server that runs on a development SQL Server.
Developers
> > > > > write
> > > > > the SRS reports. Then it comes time to deploy the reports to user
> > > > > testing.
> > > > > I have a SRS test server that may run on a test SQL server. When
it
> > comes
> > > > > time to deploy the reports into production, I have an SRS
production
> > > > > server.
> > > > > I want to find out how to best accomplish this.
> > > > >
> > > > > Thanks
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Yep. This brings up more questions though. Once you have the script(s), how
do you run them on the new server? And which server do you run them on, IIS
or SQL? I would think it would be SQL and it would be putting data into the
ReportingDB?
"Bruce L-C [MVP]" wrote:
> I have not used it. It is a tool development by a SQL Server MVP. He just
> finished it so I don't know what it is like. Remember you can create your
> own scripts to so you have lots of options. I would kick the tires on it
> some before I depend on it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> news:E2BB9DD3-011C-4648-B9E1-857950FC7227@.microsoft.com...
> > Very good Bruce and Jeff. This is what I needed to know. We have yet to
> > move to SRS, but before we do, I want some questions like this answered.
> I
> > assume you have used this tool and it works as advertised?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > This was just posted today. You might want to take a look at it.
> > > >>>>>>>>>>>>>
> > > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > > news:<#zNZyc0HFHA.2976@.TK2MSFTNGP15.phx.gbl>...
> > >
> > > Download Reporting Services Scripter
> > >
> > > http://www.sqldbatips.com/showarticle.asp?ID=62
> > >
> > >
> > > Reporting Services Scripter is a .NET Windows Forms application that
> enables
> > > scripting of all Microsoft SQL Server Reporting Services catalog items
> to
> > > aid in transferring them from one server to another. It can also be used
> to
> > > easily move items on mass from one Reporting Services folder to another
> on
> > > the same server. Depending on the scripting options chosen, Reporting
> > > Services Scripter can also transfer all catalog item properties such as
> > > Descriptions, History options, Execution options (including report
> specific
> > > and shared schedules) and server side report parameters.
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > http://www.sqldbatips.com
> > >
> > > I support PASS - the definitive, global
> > >
> > > community for SQL Server professionals -
> > >
> > > http://www.sqlpass.org
> > >
> > > >>>>>>>>>>>>>>>>
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> > > news:A944148D-6829-471D-ADA7-61C69AEB5C00@.microsoft.com...
> > > > You hit the nail on the head. How do you push the RDLs around? The
> only
> > > > thought I have on this now is the developer or the configuration
> > > management
> > > > person would have to open the report in VS.NET and publish it to a
> > > different
> > > > SRS server. Sounds messy. Maybe a better approach would be for a
> > > function
> > > > in Report Manager to push RDLs around. But then, I have to wonder
> about
> > > > versions of the RDL.
> > > >
> > > > Surely someone has thought about this. Does everyone on the planet
> code
> > > and
> > > > test on a production server?
> > > >
> > > > "Jeff A. Stucker" wrote:
> > > >
> > > > > That sounds like a good plan. The next step would be to identify
> the
> > > actual
> > > > > way you push the RDL files around. Depending on whether you are
> using
> > > forms
> > > > > auth or windows auth -- as well as how you handle configuration
> > > management
> > > > > in your company -- the best solution may vary.
> > > > >
> > > > > --
> > > > > Cheers,
> > > > >
> > > > > '(' Jeff A. Stucker
> > > > > \
> > > > >
> > > > > Business Intelligence
> > > > > www.criadvantage.com
> > > > > ---
> > > > > "Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in message
> > > > > news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
> > > > > >I would like to find some best practice documentation on how to
> manage
> > > a
> > > > > > development, test, and production configuration. For example, I
> have
> > > an
> > > > > > SRS
> > > > > > development server that runs on a development SQL Server.
> Developers
> > > > > > write
> > > > > > the SRS reports. Then it comes time to deploy the reports to user
> > > > > > testing.
> > > > > > I have a SRS test server that may run on a test SQL server. When
> it
> > > comes
> > > > > > time to deploy the reports into production, I have an SRS
> production
> > > > > > server.
> > > > > > I want to find out how to best accomplish this.
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||No, the scripting is using the published web services api. You should never
go directly against RS database. This is unsupported and dangerous. The web
services api should be used. Here is info from BOL about scripting:
____________________________________________
Script for Publishing Sample Reports
This sample script uses a script file (.rss) and the script environment to
run Web service operations on a specified report server. The script creates
a folder that you specify as a command-prompt variable using the -v switch,
and then publishes the sample reports that ship with Reporting Services to a
report server.
You can use script files with the Reporting Services Simple Object Access
Protocol (SOAP) API to access most of the management and administration
operations available on your report server.
Requirements
Before you run this sample, the following conditions must be met:
a.. The sample reports and the script file must first be installed to your
hard drive using Reporting Services Setup or the separate sample installer.
b.. You must have permission to run the rs utility from the computer on
which a report server instance is installed.
c.. You must have access to the report server that you are accessing with
your script.
d.. You must have Create Folder and Create Report permissions on the root
folder of the report server you are accessing. For more information about
permissions and user roles, see Reporting Services Security Model.
_________________________________________________________
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
news:9C9FAADA-9D26-4E6A-8061-EEF654F68E75@.microsoft.com...
> Yep. This brings up more questions though. Once you have the script(s),
how
> do you run them on the new server? And which server do you run them on,
IIS
> or SQL? I would think it would be SQL and it would be putting data into
the
> ReportingDB?
> "Bruce L-C [MVP]" wrote:
> > I have not used it. It is a tool development by a SQL Server MVP. He
just
> > finished it so I don't know what it is like. Remember you can create
your
> > own scripts to so you have lots of options. I would kick the tires on it
> > some before I depend on it.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> > news:E2BB9DD3-011C-4648-B9E1-857950FC7227@.microsoft.com...
> > > Very good Bruce and Jeff. This is what I needed to know. We have yet
to
> > > move to SRS, but before we do, I want some questions like this
answered.
> > I
> > > assume you have used this tool and it works as advertised?
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > This was just posted today. You might want to take a look at it.
> > > > >>>>>>>>>>>>>
> > > > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > > > news:<#zNZyc0HFHA.2976@.TK2MSFTNGP15.phx.gbl>...
> > > >
> > > > Download Reporting Services Scripter
> > > >
> > > > http://www.sqldbatips.com/showarticle.asp?ID=62
> > > >
> > > >
> > > > Reporting Services Scripter is a .NET Windows Forms application that
> > enables
> > > > scripting of all Microsoft SQL Server Reporting Services catalog
items
> > to
> > > > aid in transferring them from one server to another. It can also be
used
> > to
> > > > easily move items on mass from one Reporting Services folder to
another
> > on
> > > > the same server. Depending on the scripting options chosen,
Reporting
> > > > Services Scripter can also transfer all catalog item properties such
as
> > > > Descriptions, History options, Execution options (including report
> > specific
> > > > and shared schedules) and server side report parameters.
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > >
> > > > Jasper Smith (SQL Server MVP)
> > > >
> > > > http://www.sqldbatips.com
> > > >
> > > > I support PASS - the definitive, global
> > > >
> > > > community for SQL Server professionals -
> > > >
> > > > http://www.sqlpass.org
> > > >
> > > > >>>>>>>>>>>>>>>>
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > >
> > > > "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> > > > news:A944148D-6829-471D-ADA7-61C69AEB5C00@.microsoft.com...
> > > > > You hit the nail on the head. How do you push the RDLs around?
The
> > only
> > > > > thought I have on this now is the developer or the configuration
> > > > management
> > > > > person would have to open the report in VS.NET and publish it to a
> > > > different
> > > > > SRS server. Sounds messy. Maybe a better approach would be for a
> > > > function
> > > > > in Report Manager to push RDLs around. But then, I have to wonder
> > about
> > > > > versions of the RDL.
> > > > >
> > > > > Surely someone has thought about this. Does everyone on the
planet
> > code
> > > > and
> > > > > test on a production server?
> > > > >
> > > > > "Jeff A. Stucker" wrote:
> > > > >
> > > > > > That sounds like a good plan. The next step would be to
identify
> > the
> > > > actual
> > > > > > way you push the RDL files around. Depending on whether you are
> > using
> > > > forms
> > > > > > auth or windows auth -- as well as how you handle configuration
> > > > management
> > > > > > in your company -- the best solution may vary.
> > > > > >
> > > > > > --
> > > > > > Cheers,
> > > > > >
> > > > > > '(' Jeff A. Stucker
> > > > > > \
> > > > > >
> > > > > > Business Intelligence
> > > > > > www.criadvantage.com
> > > > > > ---
> > > > > > "Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in
message
> > > > > > news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
> > > > > > >I would like to find some best practice documentation on how to
> > manage
> > > > a
> > > > > > > development, test, and production configuration. For example,
I
> > have
> > > > an
> > > > > > > SRS
> > > > > > > development server that runs on a development SQL Server.
> > Developers
> > > > > > > write
> > > > > > > the SRS reports. Then it comes time to deploy the reports to
user
> > > > > > > testing.
> > > > > > > I have a SRS test server that may run on a test SQL server.
When
> > it
> > > > comes
> > > > > > > time to deploy the reports into production, I have an SRS
> > production
> > > > > > > server.
> > > > > > > I want to find out how to best accomplish this.
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||I see. Thank you again for your help Bruce
"Bruce L-C [MVP]" wrote:
> No, the scripting is using the published web services api. You should never
> go directly against RS database. This is unsupported and dangerous. The web
> services api should be used. Here is info from BOL about scripting:
> ____________________________________________
> Script for Publishing Sample Reports
> This sample script uses a script file (.rss) and the script environment to
> run Web service operations on a specified report server. The script creates
> a folder that you specify as a command-prompt variable using the -v switch,
> and then publishes the sample reports that ship with Reporting Services to a
> report server.
> You can use script files with the Reporting Services Simple Object Access
> Protocol (SOAP) API to access most of the management and administration
> operations available on your report server.
> Requirements
> Before you run this sample, the following conditions must be met:
> a.. The sample reports and the script file must first be installed to your
> hard drive using Reporting Services Setup or the separate sample installer.
> b.. You must have permission to run the rs utility from the computer on
> which a report server instance is installed.
> c.. You must have access to the report server that you are accessing with
> your script.
> d.. You must have Create Folder and Create Report permissions on the root
> folder of the report server you are accessing. For more information about
> permissions and user roles, see Reporting Services Security Model.
> _________________________________________________________
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> news:9C9FAADA-9D26-4E6A-8061-EEF654F68E75@.microsoft.com...
> > Yep. This brings up more questions though. Once you have the script(s),
> how
> > do you run them on the new server? And which server do you run them on,
> IIS
> > or SQL? I would think it would be SQL and it would be putting data into
> the
> > ReportingDB?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I have not used it. It is a tool development by a SQL Server MVP. He
> just
> > > finished it so I don't know what it is like. Remember you can create
> your
> > > own scripts to so you have lots of options. I would kick the tires on it
> > > some before I depend on it.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> > > news:E2BB9DD3-011C-4648-B9E1-857950FC7227@.microsoft.com...
> > > > Very good Bruce and Jeff. This is what I needed to know. We have yet
> to
> > > > move to SRS, but before we do, I want some questions like this
> answered.
> > > I
> > > > assume you have used this tool and it works as advertised?
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > This was just posted today. You might want to take a look at it.
> > > > > >>>>>>>>>>>>>
> > > > > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > > > > news:<#zNZyc0HFHA.2976@.TK2MSFTNGP15.phx.gbl>...
> > > > >
> > > > > Download Reporting Services Scripter
> > > > >
> > > > > http://www.sqldbatips.com/showarticle.asp?ID=62
> > > > >
> > > > >
> > > > > Reporting Services Scripter is a .NET Windows Forms application that
> > > enables
> > > > > scripting of all Microsoft SQL Server Reporting Services catalog
> items
> > > to
> > > > > aid in transferring them from one server to another. It can also be
> used
> > > to
> > > > > easily move items on mass from one Reporting Services folder to
> another
> > > on
> > > > > the same server. Depending on the scripting options chosen,
> Reporting
> > > > > Services Scripter can also transfer all catalog item properties such
> as
> > > > > Descriptions, History options, Execution options (including report
> > > specific
> > > > > and shared schedules) and server side report parameters.
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > >
> > > > > Jasper Smith (SQL Server MVP)
> > > > >
> > > > > http://www.sqldbatips.com
> > > > >
> > > > > I support PASS - the definitive, global
> > > > >
> > > > > community for SQL Server professionals -
> > > > >
> > > > > http://www.sqlpass.org
> > > > >
> > > > > >>>>>>>>>>>>>>>>
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > >
> > > > > "Lee Forst" <LeeForst@.discussions.microsoft.com> wrote in message
> > > > > news:A944148D-6829-471D-ADA7-61C69AEB5C00@.microsoft.com...
> > > > > > You hit the nail on the head. How do you push the RDLs around?
> The
> > > only
> > > > > > thought I have on this now is the developer or the configuration
> > > > > management
> > > > > > person would have to open the report in VS.NET and publish it to a
> > > > > different
> > > > > > SRS server. Sounds messy. Maybe a better approach would be for a
> > > > > function
> > > > > > in Report Manager to push RDLs around. But then, I have to wonder
> > > about
> > > > > > versions of the RDL.
> > > > > >
> > > > > > Surely someone has thought about this. Does everyone on the
> planet
> > > code
> > > > > and
> > > > > > test on a production server?
> > > > > >
> > > > > > "Jeff A. Stucker" wrote:
> > > > > >
> > > > > > > That sounds like a good plan. The next step would be to
> identify
> > > the
> > > > > actual
> > > > > > > way you push the RDL files around. Depending on whether you are
> > > using
> > > > > forms
> > > > > > > auth or windows auth -- as well as how you handle configuration
> > > > > management
> > > > > > > in your company -- the best solution may vary.
> > > > > > >
> > > > > > > --
> > > > > > > Cheers,
> > > > > > >
> > > > > > > '(' Jeff A. Stucker
> > > > > > > \
> > > > > > >
> > > > > > > Business Intelligence
> > > > > > > www.criadvantage.com
> > > > > > > ---
> > > > > > > "Lee Forst" <Lee Forst@.discussions.microsoft.com> wrote in
> message
> > > > > > > news:7830DAD0-25C8-4133-B5DE-DD5F348F8886@.microsoft.com...
> > > > > > > >I would like to find some best practice documentation on how to
> > > manage
> > > > > a
> > > > > > > > development, test, and production configuration. For example,
> I
> > > have
> > > > > an
> > > > > > > > SRS
> > > > > > > > development server that runs on a development SQL Server.
> > > Developers
> > > > > > > > write
> > > > > > > > the SRS reports. Then it comes time to deploy the reports to
> user
> > > > > > > > testing.
> > > > > > > > I have a SRS test server that may run on a test SQL server.
> When
> > > it
> > > > > comes
> > > > > > > > time to deploy the reports into production, I have an SRS
> > > production
> > > > > > > > server.
> > > > > > > > I want to find out how to best accomplish this.
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Managing database growth

Hi
I've a DB in SQL Server 2000 which allows the
database to grow by a percentage (defualt=10%).
Now,anfter any action(for example insert ing a row)
does it calculate the DB size and allocate the
accurrate size of space by defined percentage?
does it take long time and has overhead?
is it a good way to define database grow by
percentage?
Thanks for any help.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/> Now,anfter any action(for example insert ing a row)

quote:

---

> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?

---

Yes

quote:

---

> does it take long time and has overhead?

---

No

quote:

---

> is it a good way to define database grow by
> percentage?

---

Yes
Otherwise, when the database grow and somebody forget to give more space, it
will happen some ugly error.
I think if the database it some, you should let the database grow by fix
size, otherwise grow by percentage|||SQL Server doesn't do the grow after an insert. It happens whenever SQL Serv
er need space for an
insert, update etc and the file is full. The user is blocked by the grow ope
ration during the grow.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...

quote:

---

[c
olor=darkred]
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/[/color]

---

|||RM,
My personal preference is for a fixed growth unit.
Say, blocks of 1024MB for data and 512MB for log.
For some databases, excessive numbers of small growth will give you
fragmentation on the physical files.
I have seen some databases with the primary file in 300+ fragments. This
cannot be good.
Much better is a scheme something like this:
Initial size: 30GB Data / 3GB Log
Data growth: 3072MB
Log growth: 1024MB
This will minimize the number of 'expands' that need to run.
Planning is key here.
What you want to avoid is the 'ever-larger' growth syndrome.
I.e. Initial Size 30GB Data / 3GB Log.
Data growth: 10%.
Log growth 10%.
This percentage grows ever larger each time the DB expands.
Again, this is just personal preference.
James Hokes
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr2g3ovl8hqligo@.msnews.microsoft.com...

quote:

---

> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

---

Managing database growth

Hi
I've a DB in SQL Server 2000 which allows the
database to grow by a percentage (defualt=10%).
Now,anfter any action(for example insert ing a row)
does it calculate the DB size and allocate the
accurrate size of space by defined percentage?
does it take long time and has overhead?
is it a good way to define database grow by
percentage?
Thanks for any help.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
Yes
> does it take long time and has overhead?
No
> is it a good way to define database grow by
> percentage?
Yes
Otherwise, when the database grow and somebody forget to give more space, it
will happen some ugly error.
I think if the database it some, you should let the database grow by fix
size, otherwise grow by percentage|||SQL Server doesn't do the grow after an insert. It happens whenever SQL Server need space for an
insert, update etc and the file is full. The user is blocked by the grow operation during the grow.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||RM,
My personal preference is for a fixed growth unit.
Say, blocks of 1024MB for data and 512MB for log.
For some databases, excessive numbers of small growth will give you
fragmentation on the physical files.
I have seen some databases with the primary file in 300+ fragments. This
cannot be good.
Much better is a scheme something like this:
Initial size: 30GB Data / 3GB Log
Data growth: 3072MB
Log growth: 1024MB
This will minimize the number of 'expands' that need to run.
Planning is key here.
What you want to avoid is the 'ever-larger' growth syndrome.
I.e. Initial Size 30GB Data / 3GB Log.
Data growth: 10%.
Log growth 10%.
This percentage grows ever larger each time the DB expands.
Again, this is just personal preference.
James Hokes
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr2g3ovl8hqligo@.msnews.microsoft.com...
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Management Studio: Fatal Scripting Error

I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
|||This was addressed in Bug ID FDBK44155.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:ucmK$k1VGHA.5592@.TK2MSFTNGP09.phx.gbl...
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.