Deployment: SRS2005 can use SQL2K as ReportDatabase

Can the SQL Server 2005 Reporting Services configured to use the SQL2K Server
as reporting database server?
Rationale:
So that I can put the 2005 reporting services in the DMZ and use exisitng
sql2K server behind the DMZ to store the reporting database. Another reason
is that , I don't have to buy 2 licenses (1 for the reporting services and 1
for reporting database). If I have the reporting database on the SQL2K,
exisiting license would cover that.
I am not sure whether this can be done. Can somebody throw some light on this.
Thanks,Yes it's possible to store the report server database in a sql server 2000.
look at the post "RS 2005 with SQL Server 2000" (26.09.2005)
"JDS" <JDS@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F92E7419-4B02-4B8C-B63A-AFA736FA6A2F@.microsoft.com...
> Can the SQL Server 2005 Reporting Services configured to use the SQL2K
> Server
> as reporting database server?
> Rationale:
> So that I can put the 2005 reporting services in the DMZ and use exisitng
> sql2K server behind the DMZ to store the reporting database. Another
> reason
> is that , I don't have to buy 2 licenses (1 for the reporting services and
> 1
> for reporting database). If I have the reporting database on the SQL2K,
> exisiting license would cover that.
> I am not sure whether this can be done. Can somebody throw some light on
> this.
> Thanks,
>|||=?Utf-8?B?SkRT?= <JDS@.discussions.microsoft.com> wrote in
news:F92E7419-4B02-4B8C-B63A-AFA736FA6A2F@.microsoft.com:
Yes it will work BUT
You need to license SQL 2005 to use RS2005 AND since RS is not a separate
product, you need a SQL license for the computer hosting RS.
> Can the SQL Server 2005 Reporting Services configured to use the SQL2K
> Server as reporting database server?
> Rationale:
> So that I can put the 2005 reporting services in the DMZ and use
> exisitng sql2K server behind the DMZ to store the reporting database.
> Another reason is that , I don't have to buy 2 licenses (1 for the
> reporting services and 1 for reporting database). If I have the
> reporting database on the SQL2K, exisiting license would cover that.
> I am not sure whether this can be done. Can somebody throw some light
> on this.
> Thanks,
>|||Thanks Jens and Asher
"Asher_N" wrote:
> =?Utf-8?B?SkRT?= <JDS@.discussions.microsoft.com> wrote in
> news:F92E7419-4B02-4B8C-B63A-AFA736FA6A2F@.microsoft.com:
> Yes it will work BUT
> You need to license SQL 2005 to use RS2005 AND since RS is not a separate
> product, you need a SQL license for the computer hosting RS.
> > Can the SQL Server 2005 Reporting Services configured to use the SQL2K
> > Server as reporting database server?
> >
> > Rationale:
> > So that I can put the 2005 reporting services in the DMZ and use
> > exisitng sql2K server behind the DMZ to store the reporting database.
> > Another reason is that , I don't have to buy 2 licenses (1 for the
> > reporting services and 1 for reporting database). If I have the
> > reporting database on the SQL2K, exisiting license would cover that.
> >
> > I am not sure whether this can be done. Can somebody throw some light
> > on this.
> >
> > Thanks,
> >
> >
>

Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
then transfer the data). You find this in Integration Services, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hnteknik" <Henrik.F.A.Nielsen@.gmail.com> wrote in message
news:1166181741.158144.213330@.n67g2000cwd.googlegroups.com...
>I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.
Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(
|||Hi
Even if you can't restore the backup, I would expect your ISP to do it for
you.
John
"hnteknik" wrote:

> I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>
|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.The import/export wizard in 2005 doesn't expose the SQL Server specific ("sc
ript all objects and
then transfer the data). You find this in Integration Services, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hnteknik" <Henrik.F.A.Nielsen@.gmail.com> wrote in message
news:1166181741.158144.213330@.n67g2000cwd.googlegroups.com...
>I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("
script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(|||Hi
Even if you can't restore the backup, I would expect your ISP to do it for
you.
John
"hnteknik" wrote:

> I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wik...0Wizar
d

Deployment worked when it shouldn't have done

I've just had a situation where, when trying to process a cube, it failed because one of my Script Commands referenced a non-existent dimension.

Now I have no problem with that - its easily fixed. My problem is that the cube is obviously faulty so why was I allowed to deploy it? The deployment should surely have failed shouldn't it?

Is this a deployment bug?

Here is the script command:

Scope(Leaves([Currency]), EXCEPT( [Currency Conversion].[Currency Conversion].MEMBERS, {[Currency Conversion].[Currency Conversion].&[<None>]} ))

-Jamie

P.S. Does anyone from the SSAS team in Redmond frequent this forum?

The server has a very flexible, forgiving, and complex system for attempting to resolve name ambiguities. So if you simply provide the text [Currencty Conversion] it may be a dimension name or a hierarchy name or a level name or even a member name. (Yes, if the server can uniquely resolve this to a member name it will although a dimension prefix is a good idea at a minimum.) As a result, until the server has read the data during processing it does not fully bind the names and thus does not know for sure that this is a dimension name that cannot be found.

Yes, people such as myself from the SSAS team in Redmond do frequent this forum.

|||

Good to know - on both counts. Thank you Matt.

-Jamie

sql

Deployment with SQL

Hi,

I have a project that uses SQL 2000 for the database. It is finished and I am looking for the best way to deploy it via CD.
I am mainly worried about getting the SQL database to install correctly automatically and have it work correctly with the web application.
Any suggestions?

Thanks,

Mikeare you shipping sql server with your application on a cd ?

Deployment with Hardware Load balancing.

I am currently planning the deployment of reporting services into a hardware
load balanced environment. The documentation from MS only covers MS's NLB
software. Has anyone had any experience deploying with a hardware load
balancer. Specifically F5 load balencing equipment.
Thanks,
Adrian.Hello Adrian,
A report server Web farm is a collection of report server instances that
share the same report server database. Reporting Services does not provide
Web farm deployment functionality. To deploy Reporting Services in a Web
farm, you can use network load balancing (NLB) functionality provided
through Windows servers, Microsoft Application Center, or third-party
software that supports Web farm deployment. The load balancer can be
software or hardware.
For the "Steps for Deploying a Report Server Web Farm", please refer to the
"Steps for Deploying a Report Server Web Farm" section in the following web
site:
Enterprise Deployment Model
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm
/drp_deploying_v1_0h9e.asp
More information for reference:
Installing a Report Server Web Farm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/h
tm/gs_installingrs_v1_2ckm.asp
305064 How To Set Up an NLB Cluster for Application Center 2000
http://support.microsoft.com/?id=305064
Regarding the information of how to implement and maintain NLB clusters, if
you use a third-party hardware load balancer, please contact the hardware
vendor support to inquire about this issue. I believe that they will assist
you best since they will have the proper material resources to resolve this
problem.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Sophie,
To confirm that I understand what you are saying, here is my take on how you
would deploy a multi node reporting server with hardware load balancing.
Note: Database tier is being deployed as active/active SQL Cluster.
1. Deploy the initial reporting services server node as per a vanilla
install.
2. Deploy any subsequent nodes by joining the nodes to the initial reporting
services node by specifying the RSWEBFARMSERVER property in the installation
3. Overlay the web nodes with either a hardware or software load balancing
solution.
Thanks,
Adrian.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:Tu9WDs$QFHA.3704@.TK2MSFTNGXA02.phx.gbl...
> Hello Adrian,
> A report server Web farm is a collection of report server instances that
> share the same report server database. Reporting Services does not provide
> Web farm deployment functionality. To deploy Reporting Services in a Web
> farm, you can use network load balancing (NLB) functionality provided
> through Windows servers, Microsoft Application Center, or third-party
> software that supports Web farm deployment. The load balancer can be
> software or hardware.
> For the "Steps for Deploying a Report Server Web Farm", please refer to
> the
> "Steps for Deploying a Report Server Web Farm" section in the following
> web
> site:
> Enterprise Deployment Model
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm
> /drp_deploying_v1_0h9e.asp
> More information for reference:
> Installing a Report Server Web Farm
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/h
> tm/gs_installingrs_v1_2ckm.asp
> 305064 How To Set Up an NLB Cluster for Application Center 2000
> http://support.microsoft.com/?id=305064
> Regarding the information of how to implement and maintain NLB clusters,
> if
> you use a third-party hardware load balancer, please contact the hardware
> vendor support to inquire about this issue. I believe that they will
> assist
> you best since they will have the proper material resources to resolve
> this
> problem.
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||One further point, this is an Enterprise only feature.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Adrian Russell" <adrianru@.avanade.com> wrote in message
news:ujF3s%23ARFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hi Sophie,
> To confirm that I understand what you are saying, here is my take on how
you
> would deploy a multi node reporting server with hardware load balancing.
> Note: Database tier is being deployed as active/active SQL Cluster.
> 1. Deploy the initial reporting services server node as per a vanilla
> install.
> 2. Deploy any subsequent nodes by joining the nodes to the initial
reporting
> services node by specifying the RSWEBFARMSERVER property in the
installation
> 3. Overlay the web nodes with either a hardware or software load balancing
> solution.
> Thanks,
> Adrian.
> "Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
> news:Tu9WDs$QFHA.3704@.TK2MSFTNGXA02.phx.gbl...
> > Hello Adrian,
> >
> > A report server Web farm is a collection of report server instances that
> > share the same report server database. Reporting Services does not
provide
> > Web farm deployment functionality. To deploy Reporting Services in a Web
> > farm, you can use network load balancing (NLB) functionality provided
> > through Windows servers, Microsoft Application Center, or third-party
> > software that supports Web farm deployment. The load balancer can be
> > software or hardware.
> >
> > For the "Steps for Deploying a Report Server Web Farm", please refer to
> > the
> > "Steps for Deploying a Report Server Web Farm" section in the following
> > web
> > site:
> >
> > Enterprise Deployment Model
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm
> > /drp_deploying_v1_0h9e.asp
> >
> > More information for reference:
> >
> > Installing a Report Server Web Farm
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/h
> > tm/gs_installingrs_v1_2ckm.asp
> >
> > 305064 How To Set Up an NLB Cluster for Application Center 2000
> > http://support.microsoft.com/?id=305064
> >
> > Regarding the information of how to implement and maintain NLB clusters,
> > if
> > you use a third-party hardware load balancer, please contact the
hardware
> > vendor support to inquire about this issue. I believe that they will
> > assist
> > you best since they will have the proper material resources to resolve
> > this
> > problem.
> >
> > I hope the information is helpful.
> >
> >
> > Sophie Guo
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> >
> > =====================================================> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
>|||By Enterprise only I mean both SQL Server db has to be enterprise and the
Reporting Services install has to be Enterprise. Plus, for each server in
the web farm with Reporting Services you need an SQL Server Enterprise
license.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Adrian Russell" <adrianru@.avanade.com> wrote in message
news:ujF3s%23ARFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hi Sophie,
> To confirm that I understand what you are saying, here is my take on how
you
> would deploy a multi node reporting server with hardware load balancing.
> Note: Database tier is being deployed as active/active SQL Cluster.
> 1. Deploy the initial reporting services server node as per a vanilla
> install.
> 2. Deploy any subsequent nodes by joining the nodes to the initial
reporting
> services node by specifying the RSWEBFARMSERVER property in the
installation
> 3. Overlay the web nodes with either a hardware or software load balancing
> solution.
> Thanks,
> Adrian.
> "Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
> news:Tu9WDs$QFHA.3704@.TK2MSFTNGXA02.phx.gbl...
> > Hello Adrian,
> >
> > A report server Web farm is a collection of report server instances that
> > share the same report server database. Reporting Services does not
provide
> > Web farm deployment functionality. To deploy Reporting Services in a Web
> > farm, you can use network load balancing (NLB) functionality provided
> > through Windows servers, Microsoft Application Center, or third-party
> > software that supports Web farm deployment. The load balancer can be
> > software or hardware.
> >
> > For the "Steps for Deploying a Report Server Web Farm", please refer to
> > the
> > "Steps for Deploying a Report Server Web Farm" section in the following
> > web
> > site:
> >
> > Enterprise Deployment Model
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm
> > /drp_deploying_v1_0h9e.asp
> >
> > More information for reference:
> >
> > Installing a Report Server Web Farm
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/h
> > tm/gs_installingrs_v1_2ckm.asp
> >
> > 305064 How To Set Up an NLB Cluster for Application Center 2000
> > http://support.microsoft.com/?id=305064
> >
> > Regarding the information of how to implement and maintain NLB clusters,
> > if
> > you use a third-party hardware load balancer, please contact the
hardware
> > vendor support to inquire about this issue. I believe that they will
> > assist
> > you best since they will have the proper material resources to resolve
> > this
> > problem.
> >
> > I hope the information is helpful.
> >
> >
> > Sophie Guo
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> >
> > =====================================================> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
>|||I'm operating in exactly this configuration. As mentioned by others,
its really straight forward to setup:
Prerequsites:
- A Sql Server 2000 Enterprise instance with SP3a
Installation:
1) Install Sql Reporting 2000 Enterprise on the first web server.
During installation it creates the needed ReportServer and
ReportServerTempDb databases.
2) Install Sql Reporting 2000 Enterprise on each subsequent server in
Farm and point them at the ReportServer DB created in step 1. (repeat
for each additional server)
3) Deploy RDL's via ReportsServer web service from any of the servers
(since they share the same DB and therefore will share the RDL's once
they are uploaded).
4) Start using your servers. (or begin applying Service packs)
Note: SP2 will require one extra step which is to run RSActivate in
order to join your additional Report Server nodes to the group. In
pre-SP2 installations, this is done automatically for you.
One thing to consider when doing the F5 setup; make sure to setup
Server Affinity using a time-based session so you don't run into any
problems with ASP.NET post-backs. Typically I set the session
expiration to 40 minutes, but it all depends upon your
user-characteristics. Also I have found it helpful to utilize the
"Least Used" algorithm rather than the default "Round Robin" approach
to load balancing.
Obviously, there are numerous architectural and infrastructural
decisions required throughout this process, but this should give you
the 10k meter view.
I am happy with F5 for load balancing so far, so I hope this works out
for you.
Enjoy.
Lance Hunt
http://weblogs.asp.net/lhunt/|||Hello Adrian,
For the second item:
2. Deploy any subsequent nodes by joining the nodes to the initial
reporting services node by specifying the RSWEBFARMSERVER property in the
installation
The RSWEBFARMSERVER property is specified in the command prompt. To install
a web farm, you have to specify the RSDATABASENAME and RSDATABASESERVER
property in the command prompt.
For more information, please refer to the following topic in BOL:
"Installing Reporting Services From the Command Line"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/h
tm/gs_installingrs_v1_6h6h.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi!
I'm also F5 Hardware load balancing, but I'm running into a problem.
Right now, we have 2 web servers in a cluster, and an F5 load balancer
infront of it. Both web servers have Kerberos delegation enabled, and
I can access Reporting services via Web Service just fine when I access
the web servers directly. However, when I put the F5 load balancer
infront, I get an Authentication error. The load balancer appears to
be sending my credentials as Anonymous! Has this happened to anyone?
Is there a Kerberos setting on this F5 hardware load balancer? I'm
unfamiliar with the hardware, and I'm trying to contact my IT
department to contact the vendor. That might take a while. I'm hoping
someone here has an answer.
Thanks!
Roy Assaly
Sophie Guo [MSFT] wrote:
> Hello Adrian,
> For the second item:
> 2. Deploy any subsequent nodes by joining the nodes to the initial
> reporting services node by specifying the RSWEBFARMSERVER property in
the
> installation
> The RSWEBFARMSERVER property is specified in the command prompt. To
install
> a web farm, you have to specify the RSDATABASENAME and
RSDATABASESERVER
> property in the command prompt.
> For more information, please refer to the following topic in BOL:
> "Installing Reporting Services From the Command Line"
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/h
> tm/gs_installingrs_v1_6h6h.asp
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader
so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.

Deployment Version Problem

We are trying to deploy a report server project from an XP Dev box to a
Server2003 production environment.
Both computers are running SQL Server 2005 with SP1 installed.
We have tried to deploy by (1) a deploy over the network and (2) a database
backup of the ReportServer database and then a restore to the Server 2003
system.
Both methods give us exactly the same error...
The version of the report server database is either in a format that is not
valid, or it cannot be read. The found version is 'Unknown'. The expected
version is 'C.0.8.43'. To continue, update the version of the report server
database and verify access rights.
Why are we getting this error?
Regards,
GaryHi Gary,
Thank you for your posting!
Based on my research and experience, I think you need to reinstall the SP1
on the Server 2003 box.
Please let me know if you get any error message when you reinstall the SP1.
You could send the setup error log to me if you get any error. By default,
the error log file folder is C:\WINDOWS\Hotfix
Please let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Gary,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
It turned out that [apparently] the person who installed sp1 was not a
"member of the SQL Server Administrators Group" and when we followed the
recommeded fix in configuration manager the problem went away.
A mystery that remains for me is that I can't find a "SQL Server
Administrators Group". Do you know exactly what that is? Is it a user with
sysadmin privlidges?
Gary
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:t1rUdcOoGHA.2024@.TK2MSFTNGXA01.phx.gbl...
> Hi Gary,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Gary,
Thank you for the update and glad to hear the issue has been resolved.
I think the SQL Server Administrators Group means the SQL Server sysadmin
group. Members of the sysadmin fixed server role can perform any activity
in the server. By default, all members of the Windows
BUILTIN\Administrators group, the local administrator's group, are members
of the sysadmin fixed server role.
If you have any questions or concern, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Deployment 'utility' script using sqlcmd

Hi all,

I'm looking at creating a sample utility script that will invoking

scripts to deploy some SQL code. For example, a utlity script that will

run a SQL script, and on successful completion, execute the next script.

Having not used SQLCMD at all before, and being very new to SQL2005

(< 1 month) please guide me if there is a better way of invoking

this... For example, a way of avoiding the xp_cmdshell invocation!

The following code invokes a script, but I'm trying to find a way of

getting a return code back from sqlcmd, so I can progress and do the

next, or fail if the return code <> 0 (success).

[code]

--Process to create DB, Tables, and Stored Procedures

set nocount on

DECLARE

@.Error int,

@.ExecCommand varchar(512),

@.FullFilePath varchar(255)

--create the database

BEGIN TRY

SET @.FullFilePath =

'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF

Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB

TescoDNF_SSISPackageManager.sql'

SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '

SELECT @.FullFilePath

UNION

SELECT @.ExecCommand

EXEC (@.ExecCommand)

SELECT @.@.ERROR

SELECT @.Error

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

GOTO ErrorAbort

END CATCH
ErrorAbort:

[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set

of SQL scripts to, for example, a server. For example, some pseudo-code

Create Database

If Error abort

Create Table1

If Error abort

Create Table2

If Error Abort

Create Stored Procedure1

If Error Abort

ELSE Complete and report success

I do agree that this is something that could be (better) done in a

"proper" coding language like .Net, c# etc. but (a) it's just a simple

utility script (b) it teaches me moore about usage of SQLCMD and (c) I

do not have any skill in any normal programming language, hence I was

planning to write a quick deployment utility with a script.

The idea might be something as ugly as a table structure that has a

list of scripts registered in it, with some sequence logic - like

creating parent tables before children tables - and then a cursor (or a

better method if I can find it) that fetches a sqlcmd filename

execution command, executes it, and on success fetches the next one

based on the sequence logic.

I can probably do all of that in about 4 hours in T-SQL, if I can find

a way to confirm the successful execution of the previous command....|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files

and execute them, and not merge them all into a single large script.

So, I want utility script to do this:

Run external sql script

On failure abort, on Success

Run external sql script

On Failure abort, On Success

...

You being to see why I referred to a cursor?

The point is that the utility script wouldn't contain any of the client

SQL commands - it would fetch them by referring to the table, and

fetching the path to the SQL file, and building a SQLCMD to execute

that script

I guess, as you say, I could add a generic update ##SQLScriptTracker

table, then check it on the new execution, or abort. I had hoped for a

neater solution - i.e. SQLCMD being able to return a returncode that it

gets from a SQL file it ran....|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as

well, since with the batch file I have to add a file each time.

Thanks|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

Deployment 'utility' script using sqlcmd

Hi all,
I'm looking at creating a sample utility script that will invoking scripts to deploy some SQL code. For example, a utlity script that will run a SQL script, and on successful completion, execute the next script.
Having not used SQLCMD at all before, and being very new to SQL2005 (< 1 month) please guide me if there is a better way of invoking this... For example, a way of avoiding the xp_cmdshell invocation!
The following code invokes a script, but I'm trying to find a way of getting a return code back from sqlcmd, so I can progress and do the next, or fail if the return code <> 0 (success).
[code]
--Process to create DB, Tables, and Stored Procedures
set nocount on
DECLARE
@.Error int,
@.ExecCommand varchar(512),
@.FullFilePath varchar(255)
--create the database
BEGIN TRY
SET @.FullFilePath = 'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB TescoDNF_SSISPackageManager.sql'
SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '
SELECT @.FullFilePath
UNION
SELECT @.ExecCommand
EXEC (@.ExecCommand)
SELECT @.@.ERROR
SELECT @.Error
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GOTO ErrorAbort
END CATCH

ErrorAbort:
[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set of SQL scripts to, for example, a server. For example, some pseudo-code
Create Database
If Error abort
Create Table1
If Error abort
Create Table2
If Error Abort
Create Stored Procedure1
If Error Abort
ELSE Complete and report success
I do agree that this is something that could be (better) done in a "proper" coding language like .Net, c# etc. but (a) it's just a simple utility script (b) it teaches me moore about usage of SQLCMD and (c) I do not have any skill in any normal programming language, hence I was planning to write a quick deployment utility with a script.
The idea might be something as ugly as a table structure that has a list of scripts registered in it, with some sequence logic - like creating parent tables before children tables - and then a cursor (or a better method if I can find it) that fetches a sqlcmd filename execution command, executes it, and on success fetches the next one based on the sequence logic.
I can probably do all of that in about 4 hours in T-SQL, if I can find a way to confirm the successful execution of the previous command....
|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files and execute them, and not merge them all into a single large script.
So, I want utility script to do this:
Run external sql script
On failure abort, on Success
Run external sql script
On Failure abort, On Success
...
You being to see why I referred to a cursor?
The point is that the utility script wouldn't contain any of the client SQL commands - it would fetch them by referring to the table, and fetching the path to the SQL file, and building a SQLCMD to execute that script
I guess, as you say, I could add a generic update ##SQLScriptTracker table, then check it on the new execution, or abort. I had hoped for a neater solution - i.e. SQLCMD being able to return a returncode that it gets from a SQL file it ran....
|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as well, since with the batch file I have to add a file each time.
Thanks
|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

sql

Deployment Utility or Import Package?

In reference to the question raised in this thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1460591&SiteID=1

Since I'm not able to create a deployment utility, when a config file is shared among multiple packages and also I cannot get the permission from Sys Admins to use Env. Variables I'm struck.

Now I'm thinking of importing the package to Sql Server from the file system. Is there any caveats in this approach? especially regarding the config files?

[edit]
Also, do I need any special permissions to view the Integeration Services node in Management Studio? We are using Integerated Authentication, neither do I'm able to run sp_start_job sp in the msdb database.
[/edit]

Thanks

You might want to look at DTUTIL for deployment. It's a little more work than just building the project in SSIS, but it is more flexible.

With the config files, you just need to be sure they are accessible from the package once it is in SQL Server.

Deployment Utility error

I am getting the following error when trying to run a deployment utility. The server only has .net framework 2.0 and sql server 2005 (rtm) installed.
If it's any help, this server was recently upgraded from the june ctp to rtm.
This is a production server, so a solution involving as little disturbance to it's current state would be preferrable.
Thanks in advance for any suggestions.
-Jeremy
TITLE: Package Installation Wizard
Unexpected error occurred.
ADDITIONAL INFORMATION:
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
BUTTONS:
OK

Looks like the SSIS is not corectly installed on the machine. Can you uninstall SQL Server and reinstall it?

HTH,
Ovidiu

|||unfortunately, no. I'm not even sure that would have any affect, as we just installed it an hour before this error, and I saw no point in the install process where it could have gone awry.|||

We're having the exact same issue with a WinForm app deployed via ClickOnce.

What's the deal here?

Deployment Utility error

I am getting the following error when trying to run a deployment utility. The server only has .net framework 2.0 and sql server 2005 (rtm) installed.
If it's any help, this server was recently upgraded from the june ctp to rtm.
This is a production server, so a solution involving as little disturbance to it's current state would be preferrable.
Thanks in advance for any suggestions.
-Jeremy
TITLE: Package Installation Wizard
Unexpected error occurred.
ADDITIONAL INFORMATION:
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
BUTTONS:
OK

Looks like the SSIS is not corectly installed on the machine. Can you uninstall SQL Server and reinstall it?

HTH,
Ovidiu

|||unfortunately, no. I'm not even sure that would have any affect, as we just installed it an hour before this error, and I saw no point in the install process where it could have gone awry.|||

We're having the exact same issue with a WinForm app deployed via ClickOnce.

What's the deal here?

Deployment Toolkit worth using?

I want to deploy a VB.NET app along with MSDE and a sample database.
I've been trying to use the MSDE Deployment Toolkit, with help from
Mario Szpuszta's 3/04 article on MSDN. However, the article does not
include sample code, nor can I find it on the web - all the links I've
located are dead, and Szpuszta seems to have dropped off the face of
the earth. (The samples that come with the kit are NOT the code
Szpuszta refers to in the article.)
I've put together enough of Szpuszta's sample to install the framework
and MSDE as needed, but I can't get the database deployed. The article
leaves out a lot of critical info, so I'd be glad to have the sample
solution he uses.
I guess my real question at this point is: am I wasting my time? Maybe
there's a newer, more preferred way of solving the problem by now. Any
thoughts?
I'll post my own reply, for those who might want it. I located Mario's
blog and sent him a message. He replied with a link to the source for
his demonstration solution (VB.NET). I won't post the whole ugly link -
just go to GotDotNet/UserSamples and search on "MSDE".
MSDE Deployment Toolkit in Action - Sample Files

Deployment to Production problems

Somewhat newbie here trying to deploy my first Report. Production machine is
on Windows 2003 - finally got around the reporting service prompting for
windows authentication and now I'm trying to deploy my report through VS
2003. Which reports successful deployment.
If I press the "Run" button in VS 2003 it displays a web page:
[To Parent Directory]
Tuesday, March 29, 2005... Name of my report
No fancy Reporting Service front end, just a hyperlink. If I click on the
link, it displays my report.
If I navigate to http://[servername]/Reports/Pages/Folder.aspx it displays
the web page of SQL Server Reporting Services Home (with the appropriate
GUI), but no folders are listed.
There's obviously some extra step I need to do with 2003 that I didn't need
to setup on my local XP system that I developed and debugged on.Sounds like you haven't given permissions for anyone to see the reports. By
default, only admins can see contents of the server.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dtaylor" <dtaylor@.discussions.microsoft.com> wrote in message
news:F158F5D6-E52B-40F6-9F31-31C49358C33D@.microsoft.com...
> Somewhat newbie here trying to deploy my first Report. Production machine
> is
> on Windows 2003 - finally got around the reporting service prompting for
> windows authentication and now I'm trying to deploy my report through VS
> 2003. Which reports successful deployment.
> If I press the "Run" button in VS 2003 it displays a web page:
> [To Parent Directory]
> Tuesday, March 29, 2005... Name of my report
> No fancy Reporting Service front end, just a hyperlink. If I click on the
> link, it displays my report.
> If I navigate to http://[servername]/Reports/Pages/Folder.aspx it displays
> the web page of SQL Server Reporting Services Home (with the appropriate
> GUI), but no folders are listed.
> There's obviously some extra step I need to do with 2003 that I didn't
> need
> to setup on my local XP system that I developed and debugged on.sql

Deployment to one Database 'Clearing' Another...

Hi,

We have a setup whereby our production server builds to a build cube overnight, and if and when this completes successfully, and passes sanity checks, it automatically backs up and restores the cube over the top of a 'live cube'.

This seemed like a fair enough plan - it allows developers to deploy to the production server without breaking the production-live cube, however, we have noticed that deploying to the build cube database sometimes results in the clearing out of the production cube database (much to the users' dismay!).

Anyone else seen anything similar, or have any ideas as to what the problem could be?

Thanks,

Will.

First time I hear about something like that.

Try and see if you can use Synchronization functionality to deploy your changes from test to production. This is more straightforward then backup-copy-restore.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Thanks for your reply.

We currently build the cube on the production server (and 'deploy' the live cube to a databae on the same server) - we're of the view that synchronization is not possible with two databases on the same server. Is this correct?

Regards,

Will.

|||

You are right. You cannot synch from one database to another on the same server instance.

What I suggest in your case is to install another instance of Analysis Server on the same server machine. This case you achive better separation between test and production. If something wrong goes with the test instance, you can easily restart it or, it would be easier to monitor usage of the production server instance without developers interfere. Then, also you'd be able to use synch. Overall in your case second instance might be cleaner solution.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Deployment to IIS and SQL

I've written a number of reports that now need to be deployed to a production
environment. The reports will be sitting on one IIS server that is
completely hidden from SQL Server. The reports data is retrieved via a web
service.
Can the reports be deployed to IIS and SQL server independantly? If so how?Deployment is easy, you just say where it resides (the IIS portion of RS)
and RS design client within VS does the rest. The real issue for you is
installing RS in this configuration. RS is an asp.net application. Your IIS
has to be configured appropriately for this. Read up on installing where the
SQL Server is not on the same box. This is totally supported. Note that you
will still need a license for the IIS box. You have to have a license on
every box that has RS server software installed.
Bruce L-C
"Buzz" <Buzz@.discussions.microsoft.com> wrote in message
news:90E11231-A3AE-42CC-9CD3-A0F469A00AD4@.microsoft.com...
> I've written a number of reports that now need to be deployed to a
production
> environment. The reports will be sitting on one IIS server that is
> completely hidden from SQL Server. The reports data is retrieved via a
web
> service.
> Can the reports be deployed to IIS and SQL server independantly? If so
how?|||VS does not have to be installed on the Server. Only on the development
machine.
Bruce L-C
"Buzz" <Buzz@.discussions.microsoft.com> wrote in message
news:76C3CEA2-DD41-4F26-A197-8C7FA36D1F41@.microsoft.com...
> We cannot use Visual Studio to deploy in a production environment, its
just
> not installed. I am looking into using the .NET ReportingService() class
to
> deploy via a windows application or script.
> "Bruce Loehle-Conger" wrote:
> > Deployment is easy, you just say where it resides (the IIS portion of
RS)
> > and RS design client within VS does the rest. The real issue for you is
> > installing RS in this configuration. RS is an asp.net application. Your
IIS
> > has to be configured appropriately for this. Read up on installing where
the
> > SQL Server is not on the same box. This is totally supported. Note that
you
> > will still need a license for the IIS box. You have to have a license on
> > every box that has RS server software installed.
> >
> > Bruce L-C
> >
> > "Buzz" <Buzz@.discussions.microsoft.com> wrote in message
> > news:90E11231-A3AE-42CC-9CD3-A0F469A00AD4@.microsoft.com...
> > > I've written a number of reports that now need to be deployed to a
> > production
> > > environment. The reports will be sitting on one IIS server that is
> > > completely hidden from SQL Server. The reports data is retrieved via
a
> > web
> > > service.
> > >
> > > Can the reports be deployed to IIS and SQL server independantly? If
so
> > how?
> >
> >
> >

Deployment Server Properties

I am just starting into using the SQL 2005 tools and was trying to run my first model. I set up the Data sources and Data Source Views and Created a decision tree mining structure.

The properties for the deployment have the server as (local) and the database name that is valid. I am running SQLServer Express on my local PC and have verified that it is up and running.

When I try and do a Build and deploy, I get the following error:

"The project could not be deployed to the '(local)' server because of the following connection problems: A connection cannot be made. Enssure the server is running...."

Any idea what I'm doing wrong? I can deploy if I change the deployment to look at the server the data is on (a remote SQL 2005 server), but I would rather have it on my local machine. Maybe that is not a good idea. If not, please tell me.

Thanks in advance.

-Frank

Your mining model and associated objects need to be deployed to an Analysis Services 2005 instance, not to the relational database server. Since SQL Server 2005 Express does not include Analysis Services, you'll need to deploy to another server that's running Standard Edition or above and has a running instance of Analysis Services.|||

Ahh, that makes sense. Thanks.

-Frank

Deployment Security Problem

Hi,
I am trying to deploy my reports to a new server running reporting
services w/ SQL Server 2000 on the local machine. The ReportServer web site
has anonymous access NOT checked and Windows Integration selected. I'm
logged onto my machine with my domain account and when I try to deploy my
reports in VS 2003 I get "The underlying connection was closed: Could not
establish trust relationship with remote server." I do NOT have "Require
SSL" turned on and I know it has something to do with the way my credentials
are being passed (or not passed). If I turn on the option to allow
anonymous access to the reportserver site then I get a message that my
IUSR_<machinename> has insufficient privleges to perform the actions, which
is fine I don't want anonymous access on anyway.
How do I resolve deploying reports to the report server with my domain
account? I used the same domain account to install SQL Server and Reporting
Services and I am a local administrator on the server.
Thanks, ChrisIn addition to what I've discovered...
I can take the same URL I'm using for the TargetServerURL property and
browse to the report server. I get a pop-up dialog box to input my
credentials and I'm authenticated fine. Based on Microsoft Knowledge Base
Article - 842517 it would seem that the login dialog box should appear, but
it doesn't and just the error "Could not
> establish trust relationship with remote server." appears. The local
administrators are "content managers" for reporting services and I am a
member of that group, though I don't think the deployment process is making
it that far.
Thanks, Chris
"Chris" <chrisf@.unr.edu> wrote in message
news:u03AZPxfEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to deploy my reports to a new server running reporting
> services w/ SQL Server 2000 on the local machine. The ReportServer web
site
> has anonymous access NOT checked and Windows Integration selected. I'm
> logged onto my machine with my domain account and when I try to deploy my
> reports in VS 2003 I get "The underlying connection was closed: Could not
> establish trust relationship with remote server." I do NOT have "Require
> SSL" turned on and I know it has something to do with the way my
credentials
> are being passed (or not passed). If I turn on the option to allow
> anonymous access to the reportserver site then I get a message that my
> IUSR_<machinename> has insufficient privleges to perform the actions,
which
> is fine I don't want anonymous access on anyway.
> How do I resolve deploying reports to the report server with my domain
> account? I used the same domain account to install SQL Server and
Reporting
> Services and I am a local administrator on the server.
> Thanks, Chris
>|||Chris:
In IE's security settings for the zone, is User Authentication set to
automatic logon or prompt? You might want to check here (Tools ->
Options -> Security -> Custom Level (for the zone your report server
is in).
--
Scott
http://www.OdeToCode.com
On Tue, 10 Aug 2004 12:54:27 -0700, "Chris" <chrisf@.unr.edu> wrote:
>Hi,
> I am trying to deploy my reports to a new server running reporting
>services w/ SQL Server 2000 on the local machine. The ReportServer web site
>has anonymous access NOT checked and Windows Integration selected. I'm
>logged onto my machine with my domain account and when I try to deploy my
>reports in VS 2003 I get "The underlying connection was closed: Could not
>establish trust relationship with remote server." I do NOT have "Require
>SSL" turned on and I know it has something to do with the way my credentials
>are being passed (or not passed). If I turn on the option to allow
>anonymous access to the reportserver site then I get a message that my
>IUSR_<machinename> has insufficient privleges to perform the actions, which
>is fine I don't want anonymous access on anyway.
>How do I resolve deploying reports to the report server with my domain
>account? I used the same domain account to install SQL Server and Reporting
>Services and I am a local administrator on the server.
>Thanks, Chris
>|||Hey Scott,
The IE zone setting changes don't have any affect. Visual Studio
continues to report the trust relationship error. I've tried both secure
and non-secure TargetServerURL properties.
Chris
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:jp1jh0dq2m82lm982j8rab8flnjjde59o2@.4ax.com...
> Chris:
> In IE's security settings for the zone, is User Authentication set to
> automatic logon or prompt? You might want to check here (Tools ->
> Options -> Security -> Custom Level (for the zone your report server
> is in).
> --
> Scott
> http://www.OdeToCode.com
> On Tue, 10 Aug 2004 12:54:27 -0700, "Chris" <chrisf@.unr.edu> wrote:
> >Hi,
> > I am trying to deploy my reports to a new server running reporting
> >services w/ SQL Server 2000 on the local machine. The ReportServer web
site
> >has anonymous access NOT checked and Windows Integration selected. I'm
> >logged onto my machine with my domain account and when I try to deploy my
> >reports in VS 2003 I get "The underlying connection was closed: Could not
> >establish trust relationship with remote server." I do NOT have "Require
> >SSL" turned on and I know it has something to do with the way my
credentials
> >are being passed (or not passed). If I turn on the option to allow
> >anonymous access to the reportserver site then I get a message that my
> >IUSR_<machinename> has insufficient privleges to perform the actions,
which
> >is fine I don't want anonymous access on anyway.
> >
> >How do I resolve deploying reports to the report server with my domain
> >account? I used the same domain account to install SQL Server and
Reporting
> >Services and I am a local administrator on the server.
> >
> >Thanks, Chris
> >
>|||Well, I changed the security on the web site to require secure connections
and installed the certificate from the server into my machine's certificate
store and the deploy worked. I don't quite fully understand why non-ssl
connections don't work, but turning ssl on and making sure the CA was
trusted fixed the problem. Could this have been a bug or is there some
requirement that a secure connection be used to deploy reporting service
objects?
Chris
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:jp1jh0dq2m82lm982j8rab8flnjjde59o2@.4ax.com...
> Chris:
> In IE's security settings for the zone, is User Authentication set to
> automatic logon or prompt? You might want to check here (Tools ->
> Options -> Security -> Custom Level (for the zone your report server
> is in).
> --
> Scott
> http://www.OdeToCode.com
> On Tue, 10 Aug 2004 12:54:27 -0700, "Chris" <chrisf@.unr.edu> wrote:
> >Hi,
> > I am trying to deploy my reports to a new server running reporting
> >services w/ SQL Server 2000 on the local machine. The ReportServer web
site
> >has anonymous access NOT checked and Windows Integration selected. I'm
> >logged onto my machine with my domain account and when I try to deploy my
> >reports in VS 2003 I get "The underlying connection was closed: Could not
> >establish trust relationship with remote server." I do NOT have "Require
> >SSL" turned on and I know it has something to do with the way my
credentials
> >are being passed (or not passed). If I turn on the option to allow
> >anonymous access to the reportserver site then I get a message that my
> >IUSR_<machinename> has insufficient privleges to perform the actions,
which
> >is fine I don't want anonymous access on anyway.
> >
> >How do I resolve deploying reports to the report server with my domain
> >account? I used the same domain account to install SQL Server and
Reporting
> >Services and I am a local administrator on the server.
> >
> >Thanks, Chris
> >
>|||I am using integrated security without ssl. I did have a problem once where
I went through an install without unchecking ssl and I was messed up. Don't
remember how I got out of the hole. However, I have done a couple of
installs since then and made sure to un-check ssl and everything went
smoothly.
Bruce L-C
"Chris" <chrisf@.unr.edu> wrote in message
news:%23hQmER7fEHA.140@.TK2MSFTNGP12.phx.gbl...
> Well, I changed the security on the web site to require secure connections
> and installed the certificate from the server into my machine's
certificate
> store and the deploy worked. I don't quite fully understand why non-ssl
> connections don't work, but turning ssl on and making sure the CA was
> trusted fixed the problem. Could this have been a bug or is there some
> requirement that a secure connection be used to deploy reporting service
> objects?
> Chris
> "Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
> news:jp1jh0dq2m82lm982j8rab8flnjjde59o2@.4ax.com...
> > Chris:
> >
> > In IE's security settings for the zone, is User Authentication set to
> > automatic logon or prompt? You might want to check here (Tools ->
> > Options -> Security -> Custom Level (for the zone your report server
> > is in).
> >
> > --
> > Scott
> > http://www.OdeToCode.com
> >
> > On Tue, 10 Aug 2004 12:54:27 -0700, "Chris" <chrisf@.unr.edu> wrote:
> >
> > >Hi,
> > > I am trying to deploy my reports to a new server running reporting
> > >services w/ SQL Server 2000 on the local machine. The ReportServer web
> site
> > >has anonymous access NOT checked and Windows Integration selected. I'm
> > >logged onto my machine with my domain account and when I try to deploy
my
> > >reports in VS 2003 I get "The underlying connection was closed: Could
not
> > >establish trust relationship with remote server." I do NOT have
"Require
> > >SSL" turned on and I know it has something to do with the way my
> credentials
> > >are being passed (or not passed). If I turn on the option to allow
> > >anonymous access to the reportserver site then I get a message that my
> > >IUSR_<machinename> has insufficient privleges to perform the actions,
> which
> > >is fine I don't want anonymous access on anyway.
> > >
> > >How do I resolve deploying reports to the report server with my domain
> > >account? I used the same domain account to install SQL Server and
> Reporting
> > >Services and I am a local administrator on the server.
> > >
> > >Thanks, Chris
> > >
> >
>

Deployment Scripts

We are introducing a new infrastructure in work where we use depoyment
scripts rather than tweaking the db with Enterprise Manager. While I can see
the benefits of this it will be time consuming. Are there any tools out
there to automatically generate any kind of DDL script you would want. For
instance I can't find a way in Enterprise Manager/ Query Analyser to
generate logins or stuff related to jobs. Regards, Chris.Chris,
to generate logins, there is the Options tab on the Generate SQL Scripts
dialogue. For jobs, just highlight them all and right-click.
There are other things that aren't scriptable though from the GUI (linked
servers, maintenance plans, diagrams etc). For these I know of workarounds
but no simple tool. BTW in SQL Server 2005 almost anything is scriptable
from the GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Chris wrote:
> We are introducing a new infrastructure in work where we use depoyment
> scripts rather than tweaking the db with Enterprise Manager. While I can s
ee
> the benefits of this it will be time consuming. Are there any tools out
> there to automatically generate any kind of DDL script you would want. For
> instance I can't find a way in Enterprise Manager/ Query Analyser to
> generate logins or stuff related to jobs. Regards, Chris.
>
Kudos to whoever is driving this change. It may seem time consuming
now, but after you get used to it, you'll find that the GUI is actually
harder to use than writing the scripts.
While some things may not be directly scriptable from Enterprise
Manager, virtually EVERYTHING that EM does can be done using scripts,
you just have to learn the commands. One way to observe what goes on
under the covers is to use Profiler to capture the commands issued by EM
when performing various operations.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql