how to create dynamic data sources in one report

Hi all
I would like to make one report that could connect to all servers on my
network (not all on the same domain, Mixed mode authintacation).
An example of use would be to click a link for each server to view the
result set for sp_who, sp_databases, version information or anything similar.
Does anyone know a source/article that would get me going in the right
direction? What I have found so far... well I am still looking.
much thanksFrom Books Online (RS 2005). This does not work in RS 2000
>>>>>>>>
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources shares the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:73ED683D-87CE-416D-AB87-8E071E89C32F@.microsoft.com...
> Hi all
> I would like to make one report that could connect to all servers on my
> network (not all on the same domain, Mixed mode authintacation).
> An example of use would be to click a link for each server to view the
> result set for sp_who, sp_databases, version information or anything
> similar.
> Does anyone know a source/article that would get me going in the right
> direction? What I have found so far... well I am still looking.
> much thanks

How to create and schedule a job using linked servers?

There are 2 sql servers : A and C.
I have to grab data on server A and update records with this data on server C. It has to run every night, so it has to be scheduled as a job.
I created linked server on server A and ran query select... It works.
I can create Update query.
What are my steps next? I read how to create a job, but not understand how to incorporate the query into it. I am totaly unexpirenced user, please explain step by step or give me a link to a good explanation. I also do not understand how and whether DTS should be used in all this.
Appreciate any help.

Ann2wrap ur sql query in SP,create and call that SP from job steps.
From BOL,
How to create a job (Enterprise Manager)

To create a job
Expand a server group, and then expand a server.
Expand Management, and then expand SQL Server Agent.
Right-click Jobs, and then click New Job.
In the Name box, enter a name for the job.
To create a Transact-SQL job step
Create a new job or right-click an existing job, and then click Properties. For more information on creating a job, see Creating Jobs (javascript:hhobj_301.Click()).
In the Job Properties dialog box, click the Steps tab, and then click New.
In the Step name box, enter a job step name.
In the Type list, click Transact-SQL Script (TSQL).
In the Database list, click a database for this job step to use.
In the Command box, enter the Transact-SQL command batch(es), or click Open to select a Transact-SQL file to use as the command.
Click Parse to check your syntax. The message "Parse succeeded" is displayed when your syntax is correct. If an error is found, correct the syntax before continuing.|||I created a stored procedure for the job and not sure what Type and Command do I use in this case? EXEC MyProcedure in command window eith type TSQL does not work. The procedur is working fine when I run this command in Query Analyser:EXEC MyProcedure .

Thank you.
Ann

How to copy views between servers?

I want to copy some views (not data) from one server to another.
How can I do that?
Sorry! I am using Sqlserver 2005
"ad" <flying@.wfes.tcc.edu.tw> glsD:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.g bl...
>I want to copy some views (not data) from one server to another.
> How can I do that?
>
|||Hi
Probably the easiest way is to script them and run the scripts. If you are
using version control you could just run the scripts you have already
(assuming they are autonomous).
Alternatively you can use SSIS and a "transfer object" task or SMO.
John
"ad" wrote:

> Sorry! I am using Sqlserver 2005
> "ad" <flying@.wfes.tcc.edu.tw> ???g?ó?l￥ó·s?D:ePT0y4ZhIHA.3940@.TK2MSFTN GP05.phx.gbl...
>
>
|||What is SSIS?
How can I do that?
"John Bell" <jbellnewsposts@.hotmail.com> glsD:8C605E23-915D-4E44-9626-1E720FA3BE2F@.microsoft.com...[vbcol=seagreen]
> Hi
> Probably the easiest way is to script them and run the scripts. If you are
> using version control you could just run the scripts you have already
> (assuming they are autonomous).
> Alternatively you can use SSIS and a "transfer object" task or SMO.
> John
> "ad" wrote:
|||Hi
SSIS is SQL Server Integration Services, it is only available on the
Standard and Enterprise Editions and you use SQL Server Business Intelligence
Development Studio (BIDS) to write the packages. You have not said which
version of SQL Server you are using! If you are on Express or Workgroup you
would need to script the procedures or use SMO. If you don't have Books
Online it can be downloaded from
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
This has sections on SMO (just look it up in the index!)
John
"ad" wrote:

> What is SSIS?
> How can I do that?
> "John Bell" <jbellnewsposts@.hotmail.com> ???g?ó?l￥ó·s?D:8C605E23-915D-4E44-9626-1E720FA3BE2F@.microsoft.com...
>
>

How to copy views between servers?

I want to copy some views (not data) from one server to another.
How can I do that?Sorry! I am using Sqlserver 2005
"ad" <flying@.wfes.tcc.edu.tw> ¼¶¼g©ó¶l¥ó·s»D:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I want to copy some views (not data) from one server to another.
> How can I do that?
>|||Hi
Probably the easiest way is to script them and run the scripts. If you are
using version control you could just run the scripts you have already
(assuming they are autonomous).
Alternatively you can use SSIS and a "transfer object" task or SMO.
John
"ad" wrote:
> Sorry! I am using Sqlserver 2005
> "ad" <flying@.wfes.tcc.edu.tw> ¼¶¼g©ó¶l¥ó·s»D:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.gbl...
> >I want to copy some views (not data) from one server to another.
> > How can I do that?
> >
>
>|||What is SSIS?
How can I do that?
"John Bell" <jbellnewsposts@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:8C605E23-915D-4E44-9626-1E720FA3BE2F@.microsoft.com...
> Hi
> Probably the easiest way is to script them and run the scripts. If you are
> using version control you could just run the scripts you have already
> (assuming they are autonomous).
> Alternatively you can use SSIS and a "transfer object" task or SMO.
> John
> "ad" wrote:
>> Sorry! I am using Sqlserver 2005
>> "ad" <flying@.wfes.tcc.edu.tw> ?gco?l¢Do¡Ps?D:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> >I want to copy some views (not data) from one server to another.
>> > How can I do that?
>> >
>>|||Hi
SSIS is SQL Server Integration Services, it is only available on the
Standard and Enterprise Editions and you use SQL Server Business Intelligence
Development Studio (BIDS) to write the packages. You have not said which
version of SQL Server you are using! If you are on Express or Workgroup you
would need to script the procedures or use SMO. If you don't have Books
Online it can be downloaded from
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
This has sections on SMO (just look it up in the index!)
John
"ad" wrote:
> What is SSIS?
> How can I do that?
> "John Bell" <jbellnewsposts@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:8C605E23-915D-4E44-9626-1E720FA3BE2F@.microsoft.com...
> > Hi
> >
> > Probably the easiest way is to script them and run the scripts. If you are
> > using version control you could just run the scripts you have already
> > (assuming they are autonomous).
> >
> > Alternatively you can use SSIS and a "transfer object" task or SMO.
> >
> > John
> >
> > "ad" wrote:
> >
> >> Sorry! I am using Sqlserver 2005
> >>
> >> "ad" <flying@.wfes.tcc.edu.tw> ?gco?l¢Do¡Ps?D:ePT0y4ZhIHA.3940@.TK2MSFTNGP05.phx.gbl...
> >>
> >> >I want to copy some views (not data) from one server to another.
> >> > How can I do that?
> >> >
> >>
> >>
> >>
>
>

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***
"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>
|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***
|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***

How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.developersdex.com ***"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > We got a production database that gets
> >
> > tables added to it daily.
> > So, every single day we transfer those new
> >
> > tables via DTS to five (5) other servers!
> >
> > Can we accomplish the same thing using a
> >
> > 'Select' statement?
> > I found no way to use the 'select' stmt
> >
> > across servers.
> >
> > I'm not conversant with TSQL (yet!) and
> >
> > would very much appreciate any help you can
> >
> > give me.
> >
> > Thank you very much
> >
> > mike
> >
> >
> > Mike
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:
> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
> > "mike" <-nospam@.yahoo.com> wrote in message
> > news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > We got a production database that gets
> > >
> > > tables added to it daily.
> > > So, every single day we transfer those new
> > >
> > > tables via DTS to five (5) other servers!
> > >
> > > Can we accomplish the same thing using a
> > >
> > > 'Select' statement?
> > > I found no way to use the 'select' stmt
> > >
> > > across servers.
> > >
> > > I'm not conversant with TSQL (yet!) and
> > >
> > > would very much appreciate any help you can
> > >
> > > give me.
> > >
> > > Thank you very much
> > >
> > > mike
> > >
> > >
> > > Mike
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> >
> >
> > Did I understand you correctly? Your database schema changes DAILY? I know
> > nothing of your application obviously but new tables daily sounds like you
> > have some major issues that ought to be better fixed by a different
> > architecture or a better change control process.
> >
> > The best way to replicate schema changes is to script them and then apply
> > the scripts. Or invest in a tool like RedGate SQL Compare
> > (http://www.red-gate.com/). Don't you do that anyway in order to test the
> > changes?
> >
> > --
> > David Portas, SQL Server MVP
> >
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
> >
> > SQL Server Books Online:
> > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> > --
>|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
--
Adam J Warne, MCDBA
"mike" wrote:
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.developersdex.com ***
>

How to copy reports to different servers

Hi,

Once a report is deployed to a specific server (server A), is there a way to "copy" it to a different server (servers B, C), without having to actually deploy it server by server?

In my case, I am trying to have the developers deploy the reports on the development server first (server A), and then manually or automatically copy them to a Preview server (server B), and later to a production server (server C). I cannot grant the developers full permissions on the production server, so a "copy" is what I'm thinking about....

Thanks in advance for any ideas.

Edwin.

Create one Solution file and add 3 reporting project files to it with different deployment locations. That will allow your devs to test and publish reports to your dev & preview enviroments, as well as copy reports to the production project so that someone with publishing rights to your prod server can deploy them.

Remeber to practice safe source!

|||You can always try Reporting Services Scripter at http://www.sqldbatips.com/

Or you can write some code for yourself either by copying reports from server A to B using the RS webservice, or a program that takes your rdl files and deploys it to all the servers.Check out "rs utility" and "ReportingService2005 class" in Books-OnLine. I can create a small code sample for you if you want to.|||

Right-click on the solution in Solution Explorer -> Properties

You can setup multiple configuration, each with different settings for

TargetDataSourceFolder

TargetReportFolder

TargetServerURL

Default configurations include

Active(Debug)

Debug

Debug(local)

Production

Funny thing is, I just noticed this today, as I used to change the TargetReportFolder a few times within the same solution, so thanks for asking

how to copy one table from one database to another on different servers?

Hello.

I need to copy all of the rows in a table from a database on one server, to another existing table of the same name in a different database on a different server. I'm trying to use a SELECT INTO statement. Any idea how to do this?

I've tried

SELECT * INTO DestinationServer.dbo.DestinationDB.DestinationTable
FROM SourceTable AS SourceTable_1

But this doesn't work, saying there are too many prefixes.

Any idea how to do this?

If you are using SQL2000, you use DTS to copy the table or SSIS if using SQL2005. Alternatively you can use OPENROWSET in the target database to read the table in the source database.

|||

(1) SELECT .. INTO is different from INSERT INTO SELECT.

SELECT INTO tries to create the table in the INTO clause and then inserts the data from the SELECT into the table.

INSERT INTO SELECT does not create the destination table. It will directly try to insert the result of the SELECT into the destination table.

(2) The naming convention you have "DestinationServer.dbo.DestinationDB.DestinationTable" is incorrect. its ServerName.Database.dbo.Table. You have it the other way.

Depending on whether you are executing this from source or destination you have to use appropriate 4-part name (basically for whichever (source/target) is remote).

(3) Spell out all the column names if you are using INSERT INTO SELECT.

|||

Thanks guys. It's all working now.

|||how to copy one table from one database to another on different servers?

How to copy DSN from one server to another?

I need to setup identical System DSN ODBC connections on multiple servers...
is there a way to easily migrate the System DSNs from one server to another?Generally, file DSNs are used if needing to move DSNs around
to other machines (or ideally use DSN-Less connections). But
I know some people have copied system DSNs by exporting the
following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
-Sue
On Tue, 11 Jan 2005 12:03:04 -0800, Ron Niederer <Ron
Niederer@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I need to setup identical System DSN ODBC connections on multiple servers..
.
>is there a way to easily migrate the System DSNs from one server to another?[/vbcol
]|||Ron,
Please try out our free ODBC manager (www.sqledit.com/odbc) for this
purpose.
RN> I need to setup identical System DSN ODBC connections on multiple
RN> servers... is there a way to easily migrate the System DSNs from one
server to
RN> another?
Igor Shekalev, http://www.sqledit.com, powerful database tools

How to copy DSN from one server to another?

I need to setup identical System DSN ODBC connections on multiple servers...
is there a way to easily migrate the System DSNs from one server to another?
Generally, file DSNs are used if needing to move DSNs around
to other machines (or ideally use DSN-Less connections). But
I know some people have copied system DSNs by exporting the
following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
-Sue
On Tue, 11 Jan 2005 12:03:04 -0800, Ron Niederer <Ron
Niederer@.discussions.microsoft.com> wrote:

>I need to setup identical System DSN ODBC connections on multiple servers...
>is there a way to easily migrate the System DSNs from one server to another?
|||Ron,
Please try out our free ODBC manager (www.sqledit.com/odbc) for this
purpose.
RN> I need to setup identical System DSN ODBC connections on multiple
RN> servers... is there a way to easily migrate the System DSNs from one
server to
RN> another?
Igor Shekalev, http://www.sqledit.com, powerful database tools