Renaming an Access table in an SSIS package

My current project requires me to both rename the MDB file for an Access database and rename the table it contains. The Access files comes in with random names, each containing one table with a specific name. Based on the table name it contains, I rename both the file and the interior table to a standard name which a later package in the process references.

A foreach container loops through all the mdb files in the applicable directory, containing a script task and a file system task. The script task uses GetOleDbSchemaTable to extract the table name, then loops through an array of table names from the client's configuration, comparing it to a similar array of constant names and getting the matching one. The file system task then uses that found name (or the original table name if a conversion is not found) to rename the file to match that standard name. So far, so good.

Now I have to rename the table within the file as well. All of the examples of code I'm finding on the 'net refernce ADOX, but I haven't been able to figure out how to use that in a script task, assuming that's what I want to do in the first place.

Anyone have any experience with doing things like this?

Approach 1elect into a new table then drop the old table.

Approach 2: Keep the old "standard" import database and delete from the standard table, then select into it from the new database (that is, instead of renaming the existing object, just select into the desired destination object) then delete/archive the random-named database.

In the past I would have used DAO and the tabledefs collection therein to rename the table, but that is rather old-school these days. No guarantee that it would work.

|||

Thanks, Dylan. I may give the DAO a try just for giggles, because the alternative is (for now) each client having their own copy of a relatively complex package.

Renamed RS Server; unable to access site

Good evening. I just renamed the server that hosts both SQL Server and Reporting Services. I am now unable to access the report manager website (http://localhost/reports). I receive an error stating "The underlying connection was closed: The remote name could not be resolved." As suggested by previous posts, I ran the rsconfig.exe utility specifying the new server name along with the security information. I am now able to access the report server (http://localhost/reportserver) and I can successfully run reports. I am still receiving the same error when attempting to access the report manager website, however. I have searched the registry and replaced the only instance of the old server name in a key pertaining to Reporting Services and rebooted, but I still have the same problem.
Please provide any information that will resolve this problem. Thanks in advance,
KevinIn your Report Manager directory, look for a RSWebApplication.config file. Open that baby up and look for something like:
<ReportServerUrl>http://Youroldservername/ReportServer</ReportServerUrl>
You might spot your old server name there. I've been playing with that file today, which sparked the idea. Not a clue if it works or not!
Hope it helps!
- T
"Kevin" wrote:
> Good evening. I just renamed the server that hosts both SQL Server and Reporting Services. I am now unable to access the report manager website (http://localhost/reports). I receive an error stating "The underlying connection was closed: The remote name could not be resolved." As suggested by previous posts, I ran the rsconfig.exe utility specifying the new server name along with the security information. I am now able to access the report server (http://localhost/reportserver) and I can successfully run reports. I am still receiving the same error when attempting to access the report manager website, however. I have searched the registry and replaced the only instance of the old server name in a key pertaining to Reporting Services and rebooted, but I still have the same problem.
> Please provide any information that will resolve this problem. Thanks in advance,
> Kevin
>|||You need to update the server URL in the file rswebapplication.config
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:09D00359-881C-41F5-9D16-BE40D5A4023A@.microsoft.com...
> Good evening. I just renamed the server that hosts both SQL Server and
Reporting Services. I am now unable to access the report manager website
(http://localhost/reports). I receive an error stating "The underlying
connection was closed: The remote name could not be resolved." As suggested
by previous posts, I ran the rsconfig.exe utility specifying the new server
name along with the security information. I am now able to access the
report server (http://localhost/reportserver) and I can successfully run
reports. I am still receiving the same error when attempting to access the
report manager website, however. I have searched the registry and replaced
the only instance of the old server name in a key pertaining to Reporting
Services and rebooted, but I still have the same problem.
> Please provide any information that will resolve this problem. Thanks in
advance,
> Kevin
>|||That did the trick. Thanks for all of your help!
Kevin
"Brian Hartman [MSFT]" wrote:
> You need to update the server URL in the file rswebapplication.config
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:09D00359-881C-41F5-9D16-BE40D5A4023A@.microsoft.com...
> > Good evening. I just renamed the server that hosts both SQL Server and
> Reporting Services. I am now unable to access the report manager website
> (http://localhost/reports). I receive an error stating "The underlying
> connection was closed: The remote name could not be resolved." As suggested
> by previous posts, I ran the rsconfig.exe utility specifying the new server
> name along with the security information. I am now able to access the
> report server (http://localhost/reportserver) and I can successfully run
> reports. I am still receiving the same error when attempting to access the
> report manager website, however. I have searched the registry and replaced
> the only instance of the old server name in a key pertaining to Reporting
> Services and rebooted, but I still have the same problem.
> >
> > Please provide any information that will resolve this problem. Thanks in
> advance,
> > Kevin
> >
>
>|||We completed an installation of SQL Server 2000 Reporting Services and
upon trying to visit the server to publish or view the normal menu we
get only the following error:
-2147467259 (0x80004005)
The local user on the IIS server has access to the Report Database , I
can create a UDL and get a Connection Successful from the IIS box. But
I can't seem to get my normal startup page from any PC.
Thanks,
Teros wrote On 7/27/2004 4:25 PM:
> In your Report Manager directory, look for a RSWebApplication.config file. Open that baby up and look for something like:
> <ReportServerUrl>http://Youroldservername/ReportServer</ReportServerUrl>
> You might spot your old server name there. I've been playing with that file today, which sparked the idea. Not a clue if it works or not!
> Hope it helps!
> - T
>
> "Kevin" wrote:
>
>>Good evening. I just renamed the server that hosts both SQL Server and Reporting Services. I am now unable to access the report manager website (http://localhost/reports). I receive an error stating "The underlying connection was closed: The remote name could not be resolved." As suggested by previous posts, I ran the rsconfig.exe utility specifying the new server name along with the security information. I am now able to access the report server (http://localhost/reportserver) and I can successfully run reports. I am still receiving the same error when attempting to access the report manager website, however. I have searched the registry and replaced the only instance of the old server name in a key pertaining to Reporting Services and rebooted, but I still have the same problem.
>>Please provide any information that will resolve this problem. Thanks in advance,
>>Kevin

Rename MSDE table using SQL?

I am migrating a little VB6 app from Access to MSDE that
among other things has to rename some tables in a
database. That can be easily done with Enterprise
manager, but in my case has to be done programmatically
through ADO. I am wondering if this is possible, and if
so what is the syntax?
Take a look at sp_rename.
Mike
"s nikolsky" <anonymous@.discussions.microsoft.com> wrote in message
news:008c01c4aafd$480436a0$a401280a@.phx.gbl...
> I am migrating a little VB6 app from Access to MSDE that
> among other things has to rename some tables in a
> database. That can be easily done with Enterprise
> manager, but in my case has to be done programmatically
> through ADO. I am wondering if this is possible, and if
> so what is the syntax?

Re-name a Column in an Anonymous Merge Publication

Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
visible the sysobjects, so two Tables each have four Columns named: Expr1,
Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
I get errors like this:
'tblNoteAdmit' table
- Unable to rename column from 'Expr1' to 'Pump'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
table because it is published for replication.
It looks like time to Create a Script, that the Snapshot Agent can
understand ...
Sometime back, I blundered through that process to Add a Table
(Successfully) but am having trouble finding the SP_ to trick Column Names.
Aubrey Kelley
How do I Rename a Column in an Active Replicated Database?
24 hr update: Got this far: (SP is sp_rename, but still blocked by
Replication.)
USE CareData
GO
EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
Cannot rename the table because it is published for replication.
Aubrey Kelley
"Aubrey" wrote:

> Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
> visible the sysobjects, so two Tables each have four Columns named: Expr1,
> Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
> I get errors like this:
> 'tblNoteAdmit' table
> - Unable to rename column from 'Expr1' to 'Pump'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
> table because it is published for replication.
> It looks like time to Create a Script, that the Snapshot Agent can
> understand ...
> Sometime back, I blundered through that process to Add a Table
> (Successfully) but am having trouble finding the SP_ to trick Column Names.
> --
> Aubrey Kelley
|||You will have to do sp_repladdcolumn to add a dummy column with the same
data type as the column you wish to rename. Then update this temp column
with the values in the column you are wishing to rename. Then drop the
column you wish to rename using sp_repldropcolumn. Then readd the column
with the new name using sp_repladdcolumn. Then update this column with the
values in the temp column. Then drop the temp column using
sp_repldropcolumn.
Alternatively you may wish to create a temp table with the values in the
column you wish to rename along with the PK. Then drop the column you wish
to rename using sp_repldropcolumn, and then add it back with the new name
using sp_repladdcolumn. Update this new column with the values in the temp
table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...[vbcol=seagreen]
> How do I Rename a Column in an Active Replicated Database?
> 24 hr update: Got this far: (SP is sp_rename, but still blocked by
> Replication.)
> USE CareData
> GO
> EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
> Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
> Cannot rename the table because it is published for replication.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
Expr1,[vbcol=seagreen]
Database,[vbcol=seagreen]
the[vbcol=seagreen]
Names.[vbcol=seagreen]
|||Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
That was what it looked like in your SS2K T&SR Book. Was not sure it applied
to Anonymous Merge. Will try it Real-Soon-Now ...
Aubrey Kelley
"Hilary Cotter" wrote:

> You will have to do sp_repladdcolumn to add a dummy column with the same
> data type as the column you wish to rename. Then update this temp column
> with the values in the column you are wishing to rename. Then drop the
> column you wish to rename using sp_repldropcolumn. Then readd the column
> with the new name using sp_repladdcolumn. Then update this column with the
> values in the temp column. Then drop the temp column using
> sp_repldropcolumn.
> Alternatively you may wish to create a temp table with the values in the
> column you wish to rename along with the PK. Then drop the column you wish
> to rename using sp_repldropcolumn, and then add it back with the new name
> using sp_repladdcolumn. Update this new column with the values in the temp
> table.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...
> Expr1,
> Database,
> the
> Names.
>
>
|||What I did: Opened Query Analyzer
USE CareData
GO
EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
[RUN]
Results:
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'CareData' because schema replication is performed.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
The stored procedure will still be created.
Updated Values and Tested from Remote Client. Voila! Worked like a charm.
Why did you suggest a Temp column? Seemed superfluous to Copy from OldColumn
to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
works great.
Aubrey Kelley
"Aubrey" wrote:
[vbcol=seagreen]
> Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
> That was what it looked like in your SS2K T&SR Book. Was not sure it applied
> to Anonymous Merge. Will try it Real-Soon-Now ...
> --
> Aubrey Kelley
>
> "Hilary Cotter" wrote:
|||I'm glad it worked.
The temp table solution works great if you are able to kick your users off
while you do it. If not and the table was huge, the temp table could take
come time to populate and will get progressively out of sync if people are
banging away at it. So you would loose consistency as you do it.
However, my approach will "break" the table as you are making the changes so
the app which is using this column will fail as you do your renaming.
So neither solutions are perfect - mine takes longer but might guarantee
better consistency than yours. Your method is shorter, but your method is
preferred if you can kick all of your users off your system.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> What I did: Opened Query Analyzer
> USE CareData
> GO
> EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
> [RUN]
> Results:
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'CareData' because schema replication is performed.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> The stored procedure will still be created.
> Updated Values and Tested from Remote Client. Voila! Worked like a charm.
> Why did you suggest a Temp column? Seemed superfluous to Copy from
OldColumn[vbcol=seagreen]
> to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
> works great.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
AM?[vbcol=seagreen]
applied[vbcol=seagreen]
same[vbcol=seagreen]
column[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
wish[vbcol=seagreen]
name[vbcol=seagreen]
temp[vbcol=seagreen]
to make[vbcol=seagreen]
named:[vbcol=seagreen]
SQL[vbcol=seagreen]
rename[vbcol=seagreen]
Column[vbcol=seagreen]
|||Correct; I was fortunate that ALL Users are Merge Clients, No ONE is directly
updating the Publisher Database.
Just to Test, I added a couple of Rows while all this was running, and they
were missing the Values in the New Columns. Fortunately I had WHERE Clauses
in the Data Copy Form, Event Procedures just in case the Project Locked Up or
Aborted.
Now, onward and upward ... Have a Great Week!
Aubrey Kelley
"Hilary Cotter" wrote:

> I'm glad it worked.
> The temp table solution works great if you are able to kick your users off
> while you do it. If not and the table was huge, the temp table could take
> come time to populate and will get progressively out of sync if people are
> banging away at it. So you would loose consistency as you do it.
> However, my approach will "break" the table as you are making the changes so
> the app which is using this column will fail as you do your renaming.
> So neither solutions are perfect - mine takes longer but might guarantee
> better consistency than yours. Your method is shorter, but your method is
> preferred if you can kick all of your users off your system.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> OldColumn
> AM?
> applied
> same
> column
> column
> the
> the
> wish
> name
> temp
> to make
> named:
> SQL
> rename
> Column
>
>

Removing Old Databases

My company will soon be upgrading our Access Database to SQL Server 2000. In
order to begin testing and programming for the move, I have downloaded and
installed SQL2K MSDE SP3. My install went smooth and it works fine. The
problem I am having is when I am creating new databases or upgrading from
Access databases. If I decide the SQL Database that I created isn't what I
am needing then I go to the Data directory for MSDE and delete the database.
However, when I use Access to create another database by upsizing it sees
that the previous databases are still installed, as does the MSDE Manager
that I downloaded.
Question is, is there something else that I need to be doing to completely
remove the SQL databases from my system beyond deleting them from the Data
folder wherer they are initially created?
Thanks in advance.
If you mean remove the database, while keep the server (SQL Server/MSDE),
then you need detach the database from SQL Server/MSDE first. Then you can
delete the corresponding *.mdf/*.ldf files.
"digger27" <digger27@.discussions.microsoft.com> wrote in message
news:914CC09F-1341-427B-A6EC-52C4C6FCC191@.microsoft.com...
> My company will soon be upgrading our Access Database to SQL Server 2000.
In
> order to begin testing and programming for the move, I have downloaded and
> installed SQL2K MSDE SP3. My install went smooth and it works fine. The
> problem I am having is when I am creating new databases or upgrading from
> Access databases. If I decide the SQL Database that I created isn't what
I
> am needing then I go to the Data directory for MSDE and delete the
database.
> However, when I use Access to create another database by upsizing it sees
> that the previous databases are still installed, as does the MSDE Manager
> that I downloaded.
> Question is, is there something else that I need to be doing to completely
> remove the SQL databases from my system beyond deleting them from the Data
> folder wherer they are initially created?
> Thanks in advance.
|||Norman,
That was exactly the problem. Everything works great now.
"Norman Yuan" wrote:

> If you mean remove the database, while keep the server (SQL Server/MSDE),
> then you need detach the database from SQL Server/MSDE first. Then you can
> delete the corresponding *.mdf/*.ldf files.
> "digger27" <digger27@.discussions.microsoft.com> wrote in message
> news:914CC09F-1341-427B-A6EC-52C4C6FCC191@.microsoft.com...
> In
> I
> database.
>
>

Removing Administrators access to all folders

We would like to not allow our administrators to see sensitive Payroll information in Reporting Services. I have removed 'BUILTIN\Administrators' from all folders, yet those in the Domain Admins group can still see all of the adminstrative functions, as well as all of the folders. We don't want them to see some of the folders.

Does this have to do with the Domain Admins having local Admin rights on the server?

I think you have to install RS with another service, not Local Service or Networkservice, you can make the service to be installed to the name of a special user Financee\FinanceeAdministrator, after it remove builtin\administrators on the folders you dont want them to see,

|||Can we take Domain Administrator's out of the Local Administrators group on the server and get the same effect?|||Domain administrators are the superpowerful users, I dont think you can deny access to them

Removing access to see Stored Proc contents.

Howdy all. I have a 3rd party vendor (sort of) that needs to know a login and
password on a SQL server for a DB he will be accessing. He only needs to have
the ability to exec Stored Procs (SP), not write directly to tables. The
problem that Im having with this though is that I:
1; Create a login and user named Test.
2; Add Test to a DB, but assign no permissions.
3; Login as test using Enterprise Manager.
4; I can see the names of the tables.
5; If theres a way around that, Test can also open up the SP's and see the
table names there.
So now Test has a valid login/ password and knows the names of all my
tables. Is there a way to stop him from being able to see the table names for
both #4 and #5? Or any other way Im not thinking of?
TIA, ChrisR.Not with SQL 2000 but with 2005 you can.
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9FD0361F-6D5F-4A1F-B93B-7A7EB6EC651B@.microsoft.com...
> Howdy all. I have a 3rd party vendor (sort of) that needs to know a login
> and
> password on a SQL server for a DB he will be accessing. He only needs to
> have
> the ability to exec Stored Procs (SP), not write directly to tables. The
> problem that Im having with this though is that I:
> 1; Create a login and user named Test.
> 2; Add Test to a DB, but assign no permissions.
> 3; Login as test using Enterprise Manager.
> 4; I can see the names of the tables.
> 5; If theres a way around that, Test can also open up the SP's and see the
> table names there.
> So now Test has a valid login/ password and knows the names of all my
> tables. Is there a way to stop him from being able to see the table names
> for
> both #4 and #5? Or any other way Im not thinking of?
>
> TIA, ChrisR.

Removing access to see Stored Proc contents.

Howdy all. I have a 3rd party vendor (sort of) that needs to know a login an
d
password on a SQL server for a DB he will be accessing. He only needs to hav
e
the ability to exec Stored Procs (SP), not write directly to tables. The
problem that Im having with this though is that I:
1; Create a login and user named Test.
2; Add Test to a DB, but assign no permissions.
3; Login as test using Enterprise Manager.
4; I can see the names of the tables.
5; If theres a way around that, Test can also open up the SP's and see the
table names there.
So now Test has a valid login/ password and knows the names of all my
tables. Is there a way to stop him from being able to see the table names fo
r
both #4 and #5? Or any other way Im not thinking of?
TIA, ChrisR.Not with SQL 2000 but with 2005 you can.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9FD0361F-6D5F-4A1F-B93B-7A7EB6EC651B@.microsoft.com...
> Howdy all. I have a 3rd party vendor (sort of) that needs to know a login
> and
> password on a SQL server for a DB he will be accessing. He only needs to
> have
> the ability to exec Stored Procs (SP), not write directly to tables. The
> problem that Im having with this though is that I:
> 1; Create a login and user named Test.
> 2; Add Test to a DB, but assign no permissions.
> 3; Login as test using Enterprise Manager.
> 4; I can see the names of the tables.
> 5; If theres a way around that, Test can also open up the SP's and see the
> table names there.
> So now Test has a valid login/ password and knows the names of all my
> tables. Is there a way to stop him from being able to see the table names
> for
> both #4 and #5? Or any other way Im not thinking of?
>
> TIA, ChrisR.

Removing access to see Stored Proc contents.

Howdy all. I have a 3rd party vendor (sort of) that needs to know a login and
password on a SQL server for a DB he will be accessing. He only needs to have
the ability to exec Stored Procs (SP), not write directly to tables. The
problem that Im having with this though is that I:
1; Create a login and user named Test.
2; Add Test to a DB, but assign no permissions.
3; Login as test using Enterprise Manager.
4; I can see the names of the tables.
5; If theres a way around that, Test can also open up the SP's and see the
table names there.
So now Test has a valid login/ password and knows the names of all my
tables. Is there a way to stop him from being able to see the table names for
both #4 and #5? Or any other way Im not thinking of?
TIA, ChrisR.
Not with SQL 2000 but with 2005 you can.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9FD0361F-6D5F-4A1F-B93B-7A7EB6EC651B@.microsoft.com...
> Howdy all. I have a 3rd party vendor (sort of) that needs to know a login
> and
> password on a SQL server for a DB he will be accessing. He only needs to
> have
> the ability to exec Stored Procs (SP), not write directly to tables. The
> problem that Im having with this though is that I:
> 1; Create a login and user named Test.
> 2; Add Test to a DB, but assign no permissions.
> 3; Login as test using Enterprise Manager.
> 4; I can see the names of the tables.
> 5; If theres a way around that, Test can also open up the SP's and see the
> table names there.
> So now Test has a valid login/ password and knows the names of all my
> tables. Is there a way to stop him from being able to see the table names
> for
> both #4 and #5? Or any other way Im not thinking of?
>
> TIA, ChrisR.