Changing the ConnectionString property for a File Connection Manager

I have a package that I plan to run against about 700 databases to look for anomalies. I have several package variables in place that are passed in at runtime. One of them will hold the path and filename of the error log for the current database in process. I want each database to generate it's own error log for documentation and research purposes. However, when I run the package, it continues to use the path and filename that I entered when I created the File Connection Manager. I am trying to update that value in a Script Task by using the ConnectionManager class and setting the value for the "ConnectionString" property. This method is working for the OLEDB Connection Manager (which tells the package which Access database to process), but not for my File Connection Manager. Please help!

DO

Don't try and use a script task to do this. Use expressions: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

-Jamie

|||Thanks, you've done it again.

Changing lost SA password

I just started a new job and no one seems to know the SA password.
Here's my plan to change it:

1. I've run a trace for a couple of days to verify that there are no
jobs or processes that are connecting as SA and that would break if I
changed the password.

2. Before the changing the password I am going to bulk copy out the
sysxlogins row for SA so that if things go wrong I can reinset the old
data with the old unknown password (will that work?).

3. Log on to the console as Windows administrator and changing the SA
password.

Does that make sense? Anyone have any other suggestions?

Thanks.<patrickshroads@.hotmail.com> wrote in message
news:1108157543.004606.300840@.l41g2000cwc.googlegr oups.com...
>I just started a new job and no one seems to know the SA password.
> Here's my plan to change it:
> 1. I've run a trace for a couple of days to verify that there are no
> jobs or processes that are connecting as SA and that would break if I
> changed the password.
> 2. Before the changing the password I am going to bulk copy out the
> sysxlogins row for SA so that if things go wrong I can reinset the old
> data with the old unknown password (will that work?).
> 3. Log on to the console as Windows administrator and changing the SA
> password.
> Does that make sense? Anyone have any other suggestions?
> Thanks.

Assuming you've tested that the Windows Administrator account does have
sysadmin rights in MSSQL (it does by default), then you'll be able to change
the password with no problems - the only issue would be an application or
script that has the old password coded into it. But at least if something
does stop working, you know you've found a security issue which should be
fixed urgently.

As for going back to the old password, modifying data in system tables
directly is usually a bad idea and not supported by Microsoft (see "allow
updates Option" in Books Online), so if you update sysxlogins directly and
things go wrong, you'll probably have to restore master from a backup. But
it would almost certainly be better to fix whatever is using the old
password rather than messing with sysxlogins. If possible, I would set a
very strong sa password, change the server from mixed mode to Windows
authentication only, and forget about the sa login, but I appreciate that
that isn't always an option.

Simon

Changing horses in midstream...

This is NOT my choice, but I am being forced to plan for migrating my 2 SQL database clusters (SQL 2000 SP3 running on Windows 2000 Advanced Server SP4) from their current SAN environment to a new SAN environment (from EMC Clariion to IBM DS4300).

The SAN hosts a quorum disk, a data disk and a log disk for both clusters. Each cluster is a 2-node Active/Passive cluster.

I have never done anything like this before and neither has the 3rd party contract service provider charged with making it happen. They did prepare a 56-step plan for the migration (plus 14 steps for backing out), but I wondered if anyone here had any experience in this realm.

We have a 4-hour window to complete the migration; realistically that is not enough time to perform a bare-metal install (which I would almost prefer to do). Your thoughts and comments are welcome (prayers are welcome, too).

Regards,

hmscottAssuming that you have two complete sets of hardware (SAN, server, infratructure), this is relatively easy. Run both systems in parallel, loading the new system from a dump of the existing one. At a point in time near cutover, do a full dump/restore. Once you reach user downtime, do a differential backup and restore it to the new box. Change the cluster ip address of the new cluster to that of the existing cluster, and you ought to be "up and running" with little fuss, muss, or bother.

It helped that we did this three times for QA before we had to do it in production. The confidence and having the bugs worked out helped a lot.

-PatP|||Nope, I do not have 2 complete sets of hardware. I have 2 clusters currently running in production (each with 2 nodes). I do not have any additional server hardware to allocate.

I do (or will) have two complete SANs (an existing EMC Clariion CX500 and a proposed IBM DS4300).

I have raised the possibility of consolidating the two clusters prior to the migration (which would yield 2 "free" servers), but this has not yet been accepted (the two clusters operate in different subnets).

Regards,

hmscott

Assuming that you have two complete sets of hardware (SAN, server, infratructure), this is relatively easy. Run both systems in parallel, loading the new system from a dump of the existing one. At a point in time near cutover, do a full dump/restore. Once you reach user downtime, do a differential backup and restore it to the new box. Change the cluster ip address of the new cluster to that of the existing cluster, and you ought to be "up and running" with little fuss, muss, or bother.

It helped that we did this three times for QA before we had to do it in production. The confidence and having the bugs worked out helped a lot.

-PatP|||Ok, then another scenario that we looked at was to install both SANs at the same time. In other words have one SAN available as drive X and the other available as drive Y. Periodically move one database at a time using detach/attach, as database usage permits. This avoids the trauma of dropping the whole load at once, and allows some "practice" on the less critical elements.

It is relatively ugly if you need to move master, model, and msdb too. If you need to do that, then what you have to do becomes a bit more complex, but it isn't a "show stopper" either.

-PatP|||What about the Quorum disk? That's the one I am stuck on. Moving the databases doesn't bother me too much. I've done master, model, tempdb and msdb before; but I am more concerned with MSCS and how it will react.

Thanks for your quick input. I've always been grateful for the responses received here.

hmscott

Ok, then another scenario that we looked at was to install both SANs at the same time. In other words have one SAN available as drive X and the other available as drive Y. Periodically move one database at a time using detach/attach, as database usage permits. This avoids the trauma of dropping the whole load at once, and allows some "practice" on the less critical elements.

It is relatively ugly if you need to move master, model, and msdb too. If you need to do that, then what you have to do becomes a bit more complex, but it isn't a "show stopper" either.

-PatP|||The exact details escape me, but I can find them. The gist of the idea is:

1) Break the SQL cluster
2) Down the SQL Servers
3) Copy the files wholesale disk X to Y
4) Swap drive letters X with Y
5) Bring the SQL Servers back up
6) Reunite (heal) the cluster.

-PatP|||Okay, I think that's what the 3rd party service provider is also saying. I'm still trolling through MS for references on moving the quorum disk. If you know of any KBs off the top of your head, I would be grateful.

Have you done this before? Do you know anyone who has?

Again, thanks for your responses.

Regards,

hmscott

The exact details escape me, but I can find them. The gist of the idea is:

1) Break the SQL cluster
2) Down the SQL Servers
3) Copy the files wholesale disk X to Y
4) Swap drive letters X with Y
5) Bring the SQL Servers back up
6) Reunite (heal) the cluster.

-PatP|||I'd go to IBM for that information. As SAN vendors, they can get propaganda from Redmond that isn't available to mere mortals.

The problem is that from the Microsoft/SQL Server perspective, if you do this little slight of hand right, nothing at all happened. MS-PSS really doesn't do much with it, because from their perspective its a non-issue.

There's an ADC in Dallas that is quite familiar with the process. He's actually done this several times (I've only done this once). I'll see if he can scare up any documentation for the process that he can give me without needing blood first.

-PatP|||That would be most excellent. I have found a few documents on MS support site. Most are only tangentially related, though one seems pretty close to the mark (280353).

I really wish I did not have to do this, but our organization just switched to a single-source contract with IBM.

Thanks again,

hmscott

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the
course of a few days (2-5) the stored proc starts returning in about 50 sec
onds. The change is sudden, not a gradual degradation. The explain plan ha
s changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, comp
ile it (ie it is now exactly the same as it was), it then starts returning i
n 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile o
f the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query
?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the cours
e of a few days (2-5) the stored proc starts returning in about 50 seconds. The cha
nge is sudden, not a gradual degradation. The explain plan has changed. If I then
add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it
is now exactly the same as it was), it then starts returning in 4-5 seconds once again.[vbco
l=seagreen]
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this que
ry?
> TIA,
> SJT[/vbcol]
(Please reply only to the newsgroup)

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJT
Hi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT
|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
(Please reply only to the newsgroup)

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
--
(Please reply only to the newsgroup)