x

Rename scripted db objects

How can I rename all scripted object files in a folder, to replace 'dbo.' with the object type (Table, view..etc) and remove the appended object type (.Table, .view ..etc) from the file name

As an example: I have 'C:\Tables\dbo.User.Table.SQL' I want to change that to 'C:\Tables\Table User.SQL'

I tried using xp_cmdshell with rename, but get an 'Access is denied' message. My Id has sysadmin access on the server and full access to the file.

TIA
more ▼

asked Mar 16, 2012 at 08:25 PM in Default

rfonseca gravatar image

rfonseca
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

does the sql server account have full access to the file? xp_cmdshell runs in the sql server process, so it uses the same account as the sql server, not the individual executing the query.

Run the following to see the user account that needs access:

exec master..xp_cmdshell 'whoami';
more ▼

answered Mar 16, 2012 at 09:06 PM

KenJ gravatar image

KenJ
20.4k 1 4 12

(comments are locked)
10|1200 characters needed characters left
Thanks Ken. I found the account that is running the query and granted it Full Access to the file, but I still get the same message. Any other suggestions, please Thanks..
more ▼

answered Mar 19, 2012 at 04:05 PM

rfonseca gravatar image

rfonseca
0 1 1 1

is there encryption on any of the files (can you read one with xp_cmdshell), or could they still be held open by another process? You can check for handles on the files using Process Explorer - http://technet.microsoft.com/en-us/sysinternals/bb896653

Is there anything in the error message besides access denied? Can you post your script?
Mar 19, 2012 at 06:23 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x14
x14

asked: Mar 16, 2012 at 08:25 PM

Seen: 1179 times

Last Updated: Mar 19, 2012 at 06:24 PM