Can I change database in shared data source?

Hi all,
I have several reports using single shared datasource. I want to
change at a runtime database that is used by that datasource. Can this
be achieved? If not what are the other solutions - I guess that using
not shared datasource for each report may be the solution (is it?) but
it is possible for me. Can database name be changed in shared
datasource? My goal is to allow users to run the same set of reports,
viewed in ReportViewer control, but using different databases
(connection string dependant).
Thanks in advance for any suggestionsMay be to crarify a bit. I have to use shared datasource. So there are
two solutions for me. 1) Change database in shared datasource. 2)
Switch between datasources
Alas none of them appears to be possible at a runtime.
Please help.
On Mar 20, 2:54 pm, "Tomaszek" <Tomasz...@.gmail.com> wrote:
> Hi all,
> I have several reports using single shared datasource. I want to
> change at a runtime database that is used by that datasource. Can this
> be achieved? If not what are the other solutions - I guess that using
> not shared datasource for each report may be the solution (is it?) but
> it is possible for me. Can database name be changed in shared
> datasource? My goal is to allow users to run the same set of reports,
> viewed in ReportViewer control, but using different databases
> (connection string dependant).
> Thanks in advance for any suggestions|||You can do this in SQL 2005. Your datasource can be an expression (you can
have a parameter that is used to specify the database).
Check books on line. I haven't done this but I know it is a feature that was
added in 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tomaszek" <TomaszekS@.gmail.com> wrote in message
news:1174403172.440357.73260@.e1g2000hsg.googlegroups.com...
> May be to crarify a bit. I have to use shared datasource. So there are
> two solutions for me. 1) Change database in shared datasource. 2)
> Switch between datasources
> Alas none of them appears to be possible at a runtime.
> Please help.
>
> On Mar 20, 2:54 pm, "Tomaszek" <Tomasz...@.gmail.com> wrote:
>> Hi all,
>> I have several reports using single shared datasource. I want to
>> change at a runtime database that is used by that datasource. Can this
>> be achieved? If not what are the other solutions - I guess that using
>> not shared datasource for each report may be the solution (is it?) but
>> it is possible for me. Can database name be changed in shared
>> datasource? My goal is to allow users to run the same set of reports,
>> viewed in ReportViewer control, but using different databases
>> (connection string dependant).
>> Thanks in advance for any suggestions
>|||But I'm affraid that it can be expression only in non shared
datasource :(
Tommy
On Mar 20, 3:11 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You can do this in SQL 2005. Your datasource can be an expression (you can
> have a parameter that is used to specify the database).
> Check books on line. I haven't done this but I know it is a feature that was
> added in 2005.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tomaszek" <Tomasz...@.gmail.com> wrote in message
> news:1174403172.440357.73260@.e1g2000hsg.googlegroups.com...
>
> > May be to crarify a bit. I have to use shared datasource. So there are
> > two solutions for me. 1) Change database in shared datasource. 2)
> > Switch between datasources
> > Alas none of them appears to be possible at a runtime.
> > Please help.
> > On Mar 20, 2:54 pm, "Tomaszek" <Tomasz...@.gmail.com> wrote:
> >> Hi all,
> >> I have several reports using single shared datasource. I want to
> >> change at a runtime database that is used by that datasource. Can this
> >> be achieved? If not what are the other solutions - I guess that using
> >> not shared datasource for each report may be the solution (is it?) but
> >> it is possible for me. Can database name be changed in shared
> >> datasource? My goal is to allow users to run the same set of reports,
> >> viewed in ReportViewer control, but using different databases
> >> (connection string dependant).
> >> Thanks in advance for any suggestions- Hide quoted text -
> - Show quoted text -sql

Can I change a datasource's properties at report execution time?

I will be running reports on a shared database server. The database server
supports one main application with many environments for different user
groups. Each groupsâ' data is kept in a different database instance.
I want to deploy my reports in a single location on the shared database
server to simplify installing reporting services and deploying reports.
When a user requests a on-demand report I want the report to pull the data
from the correct SQL Server instance.
Can I to change the reportâ's shared datasource â'Connection Stringâ' to the
userâ's database instance. I will be using SOAP to invoke the reports.
Are there other ways to accomplish my intentions?
I am using SQL Server 2000 Reporting Services Developer Edition with SP1 and
SQL Server 2000 Standard Edition with service packs 1, 2, 3, and 3a applied.Hi,
You may want to use 'SetReportDataSources' to switch between datasources.
More information about this method can be found from the MSDN site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
rsp_prog_soapapi_intro_0dnq.asp
Sincerely,
William Wang
Microsoft Online Partner 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.
--
>Thread-Topic: Can I change a datasource's properties at report execution
time?
>thread-index: AcUz8ztCgxbpG2rJQdCyH/tlSmdIXw==>X-WBNR-Posting-Host: 192.85.47.1
>From: =?Utf-8?B?dGV4YXMzNDYw?= <texas3460@.noemail.nospam>
>Subject: Can I change a datasource's properties at report execution time?
>Date: Mon, 28 Mar 2005 16:07:01 -0800
>Lines: 17
>Message-ID: <26693252-7562-4848-9FD1-9D529F3D40F9@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 8bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:46323
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I will be running reports on a shared database server. The database
server
>supports one main application with many environments for different user
>groups. Each groupsâ' data is kept in a different database instance.
>I want to deploy my reports in a single location on the shared database
>server to simplify installing reporting services and deploying reports.
>When a user requests a on-demand report I want the report to pull the data
>from the correct SQL Server instance.
>Can I to change the reportâ's shared datasource â'Connection Stringâ'
to the
>userâ's database instance. I will be using SOAP to invoke the reports.
>Are there other ways to accomplish my intentions?
>I am using SQL Server 2000 Reporting Services Developer Edition with SP1
and
>SQL Server 2000 Standard Edition with service packs 1, 2, 3, and 3a
applied.
>

Can I back up a DataSource

I am setting up all my reports to use a shared data source which will have
its "Credentials stored securely in the report server". The datasource uses a
windows account which has the "Use as Windows credentials when connecting to
the data source" option set. The account used will have exec rights on the
appropriate stored procedures to return the data.
My question is: is there any way that I can back up this data source so that
I can restore it in the case of a server rebuild or would I have to manually
re-create it?Report Server stores everything (including the rdl for the reports) in the
database. If you backup the SQL database ReportServer you should be in good
shape.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0061CEB5-A5C2-4FF0-B9AD-B234FDFBB507@.microsoft.com...
> I am setting up all my reports to use a shared data source which will have
> its "Credentials stored securely in the report server". The datasource
uses a
> windows account which has the "Use as Windows credentials when connecting
to
> the data source" option set. The account used will have exec rights on the
> appropriate stored procedures to return the data.
> My question is: is there any way that I can back up this data source so
that
> I can restore it in the case of a server rebuild or would I have to
manually
> re-create it?
>
>

Can I access data source properties?

Can I, in an expression, access components of a shared data source? I'd like
to be able to get the DSN . . .
Alternatively, is there a way to dynamically set the database schema portion
of a query? I have been able to replace it with a REPORT parameter. Is
there a way to set some sort of a PROJECT level parameter?Yes you can. Go to the dataset properties and choose not to use a shared
datasource. You can then choose the database driver and then set the
connection string. This connection string can be an expression you can put
whatever you like in there. I used custom code which accesses an XML file
which contained all the different database connections for each company.
Each report then uses this XML file to set its connection string, hence
being a Project level setting.
Craig
"LoveBoxers" <LoveBoxers@.discussions.microsoft.com> wrote in message
news:DB650136-EF8D-42BB-8525-F9BD794F7BDD@.microsoft.com...
> Can I, in an expression, access components of a shared data source? I'd
> like
> to be able to get the DSN . . .
> Alternatively, is there a way to dynamically set the database schema
> portion
> of a query? I have been able to replace it with a REPORT parameter. Is
> there a way to set some sort of a PROJECT level parameter?|||This is good news! Would it be possible to send me an example of the
expression in the connection string and what the XML file needs to contain?
Does the XML file become part of the project/solution? When the report(s) is
"deployed", how does the XML file become a part of the package?
Thanks for your help!
"Craig" wrote:
> Yes you can. Go to the dataset properties and choose not to use a shared
> datasource. You can then choose the database driver and then set the
> connection string. This connection string can be an expression you can put
> whatever you like in there. I used custom code which accesses an XML file
> which contained all the different database connections for each company.
> Each report then uses this XML file to set its connection string, hence
> being a Project level setting.
> Craig
> "LoveBoxers" <LoveBoxers@.discussions.microsoft.com> wrote in message
> news:DB650136-EF8D-42BB-8525-F9BD794F7BDD@.microsoft.com...
> > Can I, in an expression, access components of a shared data source? I'd
> > like
> > to be able to get the DSN . . .
> >
> > Alternatively, is there a way to dynamically set the database schema
> > portion
> > of a query? I have been able to replace it with a REPORT parameter. Is
> > there a way to set some sort of a PROJECT level parameter?
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00B3_01C649AF.F31964F0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
OK example as below:
Here is the connection string: (I am using an Oracle Database and =define it by specifying a company code and an environment)
=3DCode.m_RR.GetConnectionString(Parameters!COMPANY_CODE.Value,Parameters=!ENVIRONMENT.Value)
m_RR is the instance name for my custom code I have attached to the =report (via Report References)
The XML excerpt then looks something like this (note these connections =are Oracle by you can put anything in the connection string):
<Connections default=3D"Data Source=3Dabc;Unicode=3DTrue">
<Environment name=3D"DEV" default=3D"Data Source=3Dabc;Unicode=3DTrue">
<Company code=3D"351" connection=3D"Data Source=3Dabc;Unicode=3DTrue"/>
</Environment>
<Environment name=3D"TEST" default=3D"Data Source=3Dabc;Unicode=3DTrue">
<Company code=3D"171" connection=3D"Data Source=3Ddef;Unicode=3DTrue"/>
<Company code=3D"201" connection=3D"Data Source=3Dghi;Unicode=3DTrue"/>
<Company code=3D"181" connection=3D"Data Source=3Djkl;Unicode=3DTrue"/>
<Company code=3D"311" connection=3D"Data Source=3Dmno;Unicode=3DTrue"/>
<Company code=3D"211" connection=3D"Data Source=3Dpqr;Unicode=3DTrue"/>
<Company code=3D"216" connection=3D"Data Source=3Dstu;Unicode=3DTrue"/>
</Environment>
<Environment name=3D"PROD" default=3D"">
</Environment>
</Connections>
So you can then write any C# code you like to access the XML and read =the connection strings:
as (Sorry about the formatting)...
public string GetConnectionString(string sCompanyCode, string =sEnvironment)
{
string sTempConnection;
if (m_Connection !=3D "") return m_Connection;
try
{
FileIOPermission perm =3D new =FileIOPermission(System.Security.Permissions.PermissionState.Unrestricted=);
perm.Assert();
}
catch
{
//Assertion failed. No use continuing, just return default.
return "Data Source=3Dabc;Unicode=3DTrue";
}
if (m_Settings =3D=3D null)
{
m_Settings =3D new XmlDocument();
try
{
string path =3D Environment.CurrentDirectory + "\\Settings.xml";
m_Settings.Load(path);
}
catch
{
//Failed to find xml document or something is wrong with it.
//Set the default connection
m_Connection =3D "Data Source=3Dabc;Unicode=3DTrue";
m_Settings =3D null;
return m_Connection;
}
}
XmlNodeList oConnections =3D =m_Settings.GetElementsByTagName("Connections");
if (oConnections !=3D null)
{
sTempConnection =3D m_Connection;
XmlNode oConnection =3D oConnections[0];
try
{
m_Connection =3D oConnection.Attributes["default"].Value;
}
catch
{
//No default attribute found
m_Connection =3D sTempConnection;
}
if (oConnection.ChildNodes !=3D null && oConnection.ChildNodes.Count > =0)
{
foreach (XmlElement oEnvironment in oConnection.ChildNodes)
{
if (oEnvironment.Name !=3D "Environment") continue;
sTempConnection =3D m_Connection;
try
{
if (oEnvironment.Attributes["name"].Value =3D=3D sEnvironment)
{
sTempConnection =3D m_Connection;
try
{
m_Connection =3D oEnvironment.Attributes["default"].Value;
}
catch
{
//No default attribute found
m_Connection =3D sTempConnection;
}
if (oEnvironment.ChildNodes !=3D null && oEnvironment.ChildNodes.Count > =0)
{
foreach (XmlElement oCompany in oEnvironment.ChildNodes)
{
if (oCompany.Name !=3D "Company") continue;
sTempConnection =3D m_Connection;
try
{
if (oCompany.Attributes["code"].Value =3D=3D sCompanyCode)
{
m_Connection =3D oCompany.Attributes["connection"].Value;
}
}
catch
{
//Do nothing just continue on to next record
m_Connection =3D sTempConnection;
}
}
}
}
}
catch
{
//Do nothing just continue on to next record
m_Connection =3D sTempConnection;
}
}
}
}
else
{
//No environments setup so set default
m_Connection =3D "Data Source=3Dabc;Unicode=3DTrue";
}
return m_Connection;
}
I placed a post build event on the project so that it copies the XML =document in to the previewer directory of SSRS. Also note that when you =deploy this to production you will also need to copy the XML file in to =the running directory of SSRS.
Hope this helps,
Craig
"LoveBoxers" <LoveBoxers@.discussions.microsoft.com> wrote in message =news:5805CDA2-C5E8-4D70-BDBD-9F1F0DEA3948@.microsoft.com...
> This is good news! Would it be possible to send me an example of the > expression in the connection string and what the XML file needs to =contain? > Does the XML file become part of the project/solution? When the =report(s) is > "deployed", how does the XML file become a part of the package?
> > Thanks for your help!
> > "Craig" wrote:
> >> Yes you can. Go to the dataset properties and choose not to use a =shared >> datasource. You can then choose the database driver and then set the =
>> connection string. This connection string can be an expression you =can put >> whatever you like in there. I used custom code which accesses an XML =file >> which contained all the different database connections for each =company. >> Each report then uses this XML file to set its connection string, =hence >> being a Project level setting.
>> >> Craig
>> >> "LoveBoxers" <LoveBoxers@.discussions.microsoft.com> wrote in message >> news:DB650136-EF8D-42BB-8525-F9BD794F7BDD@.microsoft.com...
>> > Can I, in an expression, access components of a shared data source? = I'd >> > like
>> > to be able to get the DSN . . .
>> >
>> > Alternatively, is there a way to dynamically set the database =schema >> > portion
>> > of a query? I have been able to replace it with a REPORT =parameter. Is
>> > there a way to set some sort of a PROJECT level parameter? >> >> --=_NextPart_000_00B3_01C649AF.F31964F0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

OK example as below:
Here is the connection string: (I =am using an Oracle Database and define it by specifying a company code and an environment)
=3DCode.m_RR.GetConnectionString(Parameters!COMPANY_CODE.Value,P=arameters!ENVIRONMENT.Value)
m_RR is the instance name for my custom =code I have attached to the report (via Report References)
The XML excerpt then looks something =like this (note these connections are Oracle by you can put anything in the =connection string):
So you can then write any C# code you =like to access the XML and read the connection strings:
as (Sorry about the =formatting)...
public =string =GetConnectionString(string sCompanyCode, =string =sEnvironment)
{
string sTempConnection;
if =(m_Connection !=3D "") =return =m_Connection;
try
{
FileIOPermission perm =3D new =FileIOPermission(System.Security.Permissions.PermissionState.Unrestricted);
perm.Assert();
}
catch
{
//Assertion failed. No use =continuing, just return default.
return ="Data Source=3Dabc;Unicode=3DTrue";
}
if =(m_Settings =3D=3D null)
{
m_Settings =3D new XmlDocument();
try
{
string =path =3D Environment.CurrentDirectory + "\\Settings.xml";
m_Settings.Load(path);
}
catch
{
//Failed to find xml document =or something is wrong with it.
//Set the default connection
m_Connection =3D "Data Source=3Dabc;Unicode=3DTrue";
m_Settings =3D null;
return m_Connection;
}
}
XmlNodeList oConnections =3D m_Settings.GetElementsByTagName("Connections");
if =(oConnections !=3D null)
{
sTempConnection =3D m_Connection;
XmlNode =oConnection =3D oConnections[0];
try
{
m_Connection =3D oConnection.Attributes["default"].Value;
}
catch
{
//No default attribute found
m_Connection =3D sTempConnection;
}
if (oConnection.ChildNodes !=3D null && oConnection.ChildNodes.Count > 0)
{
foreach =(XmlElement oEnvironment =in =oConnection.ChildNodes)
{
if =(oEnvironment.Name !=3D "Environment") continue;
sTempConnection =3D m_Connection;
try
{
if (oEnvironment.Attributes["name"].Value =3D=3D sEnvironment)
{
sTempConnection =3D m_Connection;
try
{
m_Connection =3D oEnvironment.Attributes["default"].Value;
}
catch
{
//No default attribute found
m_Connection =3D sTempConnection;
}
if (oEnvironment.ChildNodes !=3D null && oEnvironment.ChildNodes.Count > 0)
{
foreach =(XmlElement oCompany =in =oEnvironment.ChildNodes)
{
if =(oCompany.Name !=3D "Company") =continue;
sTempConnection =3D m_Connection;
try
{
if (oCompany.Attributes["code"].Value =3D=3D sCompanyCode)
{
m_Connection =3D oCompany.Attributes["connection"].Value;
}
}
catch
{
//Do nothing just continue on =to next record
m_Connection =3D sTempConnection;
}
}
}
}
}
catch
{
//Do nothing just continue on =to next record
m_Connection =3D sTempConnection;
}
}
}
}
else
{
//No environments setup so =set default
m_Connection =3D "Data Source=3Dabc;Unicode=3DTrue";
}
return m_Connection;
}
I placed a post build event on the =project so that it copies the XML document in to the previewer directory of SSRS. =Also note that when you deploy this to production you will also need to copy the =XML file in to the running directory of SSRS.
Hope this helps,
Craig
"LoveBoxers" wrote in message news:5805CDA2-C5E8-4D70-BDBD-9F1F0DEA3948@.microsoft.com...> =This is good news! Would it be possible to send me an example of the > expression in the connection string and what the XML file needs to contain? > Does the XML file become part of the project/solution? When the report(s) is > "deployed", how =does the XML file become a part of the package?> > Thanks for your help!> > "Craig" wrote:> > Yes you =can. Go to the dataset properties and choose not to use a shared > datasource. You can then choose the database driver and then set =the > connection string. This connection string can be an expression you can put > whatever you like in there. I =used custom code which accesses an XML file > which contained all =the different database connections for each company. > Each =report then uses this XML file to set its connection string, hence > =being a Project level setting.> > Craig> => "LoveBoxers" wrote in message > news:DB650136-EF8D-42BB-8525-F9BD794F7BDD@.microsoft.com...> > Can I, in an expression, =access components of a shared data source? I'd > > =like> > to be able to get the DSN . . .> >> > Alternatively, is there a way to dynamically set the database schema > > portion> > of a query? I have been =able to replace it with a REPORT parameter. Is> > there a =way to set some sort of a PROJECT level parameter? > > >

---

--=_NextPart_000_00B3_01C649AF.F31964F0--

can dbcc showcontig slow things down/ lock tables cont'd

Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that
they will be locked for a shorter period of time?
TIA, ChrisR
Yes. Shared locks are put up when you run it. Try using WITH FAST and do
only one table at a time. You get less info, but it's the stuff you need.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"ChrisR" <noemail@.bla.com> wrote in message
news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
sql2k sp3a
Not really any more to ask.
TIA, ChrisR
" A fast scan does not read the leaf or data level pages of the index. "
http://msdn.microsoft.com/library/de..._dbcc_46cn.asp
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"ChrisR" <noemail@.bla.com> wrote in message
news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that
> they will be locked for a shorter period of time?
> TIA, ChrisR
>
>
> Yes. Shared locks are put up when you run it. Try using WITH FAST and do
> only one table at a time. You get less info, but it's the stuff you need.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
> sql2k sp3a
> Not really any more to ask.
> TIA, ChrisR
>
>
|||Thanks Adam. I saw that, and the way I read it is that it won't lock my
tables up. But before I fire this off in production during the middle of the
day, I wanted to confirm my thoughts?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCIS9nMqFHA.208@.TK2MSFTNGP10.phx.gbl...
>" A fast scan does not read the leaf or data level pages of the index. "
> http://msdn.microsoft.com/library/de..._dbcc_46cn.asp
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
>
|||FAST makes the operation faster as the leaf level doesn't have to be read. As for locking FAST
doesn't make a difference (table S lock). For non-heaps, you will have table IS lock instead when
using FAST.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <noemail@.bla.com> wrote in message news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that they will be locked for a
> shorter period of time?
> TIA, ChrisR
>
>
> Yes. Shared locks are put up when you run it. Try using WITH FAST and do
> only one table at a time. You get less info, but it's the stuff you need.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
> sql2k sp3a
> Not really any more to ask.
> TIA, ChrisR
>
>
|||It will still acquire shared locks.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"ChrisR" <noemail@.bla.com> wrote in message
news:umO5mqMqFHA.1028@.TK2MSFTNGP09.phx.gbl...
> Thanks Adam. I saw that, and the way I read it is that it won't lock my
> tables up. But before I fire this off in production during the middle of
the[vbcol=seagreen]
> day, I wanted to confirm my thoughts?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCIS9nMqFHA.208@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de..._dbcc_46cn.asp[vbcol=seagreen]
that
>
|||Oops, a mistake in my post:
For heaps, FAST doesn't make difference (S table lock).
For non-heaps, FAST takes table IS instead of table S.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:OJs0rsMqFHA.3576@.TK2MSFTNGP09.phx.gbl...
> FAST makes the operation faster as the leaf level doesn't have to be read. As for locking FAST
> doesn't make a difference (table S lock). For non-heaps, you will have table IS lock instead when
> using FAST.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <noemail@.bla.com> wrote in message news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
>

can dbcc showcontig slow things down/ lock tables cont'd

Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that
they will be locked for a shorter period of time?
TIA, ChrisR
Yes. Shared locks are put up when you run it. Try using WITH FAST and do
only one table at a time. You get less info, but it's the stuff you need.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"ChrisR" <noemail@.bla.com> wrote in message
news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
sql2k sp3a
Not really any more to ask.
TIA, ChrisR" A fast scan does not read the leaf or data level pages of the index. "
http://msdn.microsoft.com/library/d...
6cn.asp
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ChrisR" <noemail@.bla.com> wrote in message
news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that
> they will be locked for a shorter period of time?
> TIA, ChrisR
>
>
> Yes. Shared locks are put up when you run it. Try using WITH FAST and do
> only one table at a time. You get less info, but it's the stuff you need.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
> sql2k sp3a
> Not really any more to ask.
> TIA, ChrisR
>
>|||Thanks Adam. I saw that, and the way I read it is that it won't lock my
tables up. But before I fire this off in production during the middle of the
day, I wanted to confirm my thoughts?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCIS9nMqFHA.208@.TK2MSFTNGP10.phx.gbl...
>" A fast scan does not read the leaf or data level pages of the index. "
> http://msdn.microsoft.com/library/d...r />
_46cn.asp
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...
>|||FAST makes the operation faster as the leaf level doesn't have to be read. A
s for locking FAST
doesn't make a difference (table S lock). For non-heaps, you will have table
IS lock instead when
using FAST.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <noemail@.bla.com> wrote in message news:OgEFzkMqFHA.544@.TK2MSFTNGP11.phx.gbl...[vbc
ol=seagreen]
> Thanks Tom. Does WITH FAST mean that the tables won't be locked, or that t
hey will be locked for a
> shorter period of time?
> TIA, ChrisR
>
>
> Yes. Shared locks are put up when you run it. Try using WITH FAST and do
> only one table at a time. You get less info, but it's the stuff you need.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
> sql2k sp3a
> Not really any more to ask.
> TIA, ChrisR
>
>[/vbcol]|||It will still acquire shared locks.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ChrisR" <noemail@.bla.com> wrote in message
news:umO5mqMqFHA.1028@.TK2MSFTNGP09.phx.gbl...
> Thanks Adam. I saw that, and the way I read it is that it won't lock my
> tables up. But before I fire this off in production during the middle of
the
> day, I wanted to confirm my thoughts?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCIS9nMqFHA.208@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/d...s_dbcc_46cn.asp[vb
col=seagreen]
that[vbcol=seagreen]
>|||Oops, a mistake in my post:
For heaps, FAST doesn't make difference (S table lock).
For non-heaps, FAST takes table IS instead of table S.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:OJs0rsMqFHA.3576@.TK2MSFTNGP09.phx.gbl...
> FAST makes the operation faster as the leaf level doesn't have to be read.
As for locking FAST
> doesn't make a difference (table S lock). For non-heaps, you will have tab
le IS lock instead when
> using FAST.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <noemail@.bla.com> wrote in message news:OgEFzkMqFHA.544@.TK2MSFTNG
P11.phx.gbl...
>

Can anyone offer me any suggestions on how to create a back up site w/ db in the

I am currently running a site on a shared environment with sql db. The account is with verio.com: Windows Server 2003m SQL 2000.

What we would like to do is create a back up site with a backup db. I would like this functionality so that in the event of a crash, while the current site is being repaired, or if the server is down for a prolonged period, I can simply switch to the backup site w/ db.

What would the preferable method of doing this in such an environment? Any help would be greatly appreciated. Thanks.Look into Log Shipping, sounds like it may answer your needs.|||I just read up on that at:

http://www.sql-server-performance.com/sql_server_log_shipping.asp

Very interesting...however can I pull that off in a shared environment, even if I have to get a second account?|||I don't see any issue with LS working in shared environment.
Can you explain about second account?|||Originally posted by rdjabarov
Look into Log Shipping, sounds like it may answer your needs.

Can 2005 and 2000 have use same shared network drive as resource

Can 2005 and 2000 have use same shared network drive as resource?
Example: If 2000 uses I,J,K Network drives can 2005 use I:\2005,j:\2005 and
k:\2005 .
We have A/A cluster, Can i upgrade one instance to 2005 and keep another as
2000 for some time.
Thanks
IT
"IT" <IT@.discussions.microsoft.com> wrote in message
news:2B430FAC-5A0E-4F65-B28F-6C51D1AD31EA@.microsoft.com...
> Can 2005 and 2000 have use same shared network drive as resource?
Not if they are configured correctly. Since each instance will have its own
cluster group and thus its own virtual server, then the resources from one
cluster group can not be used by another cluster group at the same time.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th

> Example: If 2000 uses I,J,K Network drives can 2005 use I:\2005,j:\2005
> and
> k:\2005 .
> We have A/A cluster, Can i upgrade one instance to 2005 and keep another
> as
> 2000 for some time.
> Thanks
> IT
>
|||Thank you for the reply. I have one more question on A/A cluster(right now
its sql 2000 A/A cluster), Can i upgrade one instance to 2005 and keep
another as 2000 for some time.
Thanks in advance.
"Russ Kaufmann" wrote:

> "IT" <IT@.discussions.microsoft.com> wrote in message
> news:2B430FAC-5A0E-4F65-B28F-6C51D1AD31EA@.microsoft.com...
> Not if they are configured correctly. Since each instance will have its own
> cluster group and thus its own virtual server, then the resources from one
> cluster group can not be used by another cluster group at the same time.
>
> --
> Russ Kaufmann
> MVP - Windows Server - Clustering
> ClusterHelp.com, a Microsoft Certified Gold Partner
> Web http://www.clusterhelp.com
> Blog http://msmvps.com/clusterhelp
> The next ClusterHelp classes are:
> Denver starting Feb 12th
> NYC starting Feb 19th
>
>
|||"IT" <IT@.discussions.microsoft.com> wrote in message
news:60AC40F5-FB03-4265-9BDF-2174C196DF81@.microsoft.com...
> Thank you for the reply. I have one more question on A/A cluster(right now
> its sql 2000 A/A cluster), Can i upgrade one instance to 2005 and keep
> another as 2000 for some time.
Sure. You can have different versions of SQL running in different instances.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th