July 25, 2008 at 6:55 am
When you setup the alias name was that done in DNS so that everyone would be able to find this file server as the alias or was this only completed on the SQL Server via the Hosts file? Based on the scenario I'm guessing that this was a change made on the DNS so that anyone could use the "\\alias" to get there.
It's possible that you need to refresh the local DNS table on the SQL Server. Open command line and run these commands individually:
ipconfig /flushdns
ipconfig /registerdns
Wait a little while and attempt the operation again and see if you get different results.
Regards, Irish
July 25, 2008 at 7:18 am
Jeffrey, thanks for the suggestion. However, been there, done that.
It is in DNS, so everyone can get to it, and the host that running the maintenance plan has the correct DNS pointer as demonstrated by it's ability to access \\alias from anyplace but the extended store procedure
Jim
July 29, 2008 at 12:05 pm
For kicks and grins (a.k.a. troubleshooting purposes) try adding that file server to the hosts file and see if that works. If it does, then perhaps SQL Server is misinterpreting your command or is not able to fully bind to TCP/IP.
Regards, Irish
July 30, 2008 at 12:51 pm
Jeffrey, I work with Jim, and tried that too. No luck.
July 30, 2008 at 12:57 pm
Are you using a FQDN in there for the alias?
July 30, 2008 at 1:02 pm
In the stored procedure, I tried passing both the alias and the FQDN version of the alias with the same result..
I have not done any testing yet with the HOSTS file in the lab. I will give that a try later today
July 30, 2008 at 1:14 pm
Hmm, this is a head scratcher to say the least.
Okay, so now I have to stop outside the box and look at this again.
So the message says that the account that runs the SQL Agent does not have rights to create a new directory. Maybe this is the issue?
What kind of Server is this shared directory on? I tried to duplicate this here, with host files rather than DNS so it's not exactly the same, and I could not duplicate the error. However, I had SQL on both Servers with the same Agent Account.
My theory is that the rights are not quite right, but I am not sure why.
Regards, Irish
July 30, 2008 at 1:22 pm
One correction, it is a DNS CNAME alais, not an A record.
Just checked security, even at the DNS level, not knowing if it has to be applied there too, but I did. All is correct.
When I change the FQDN to the alias in Enterprise manager, I get the error "The directory you have specified is invalid. Are you sure this is what you want?"
When I say yes, the job fails.
July 30, 2008 at 1:25 pm
Server 2003 standard edition SP2.
July 30, 2008 at 1:27 pm
So the directory is perceived as invalid.
If you use the IP address of the server, that would be independent of DNS.
Some things to try, RDP to a Server as the user that runs the SQL Agent Service. Once connected try to connect to that file share using the various methods you've listed fro the run prompt. Somethings got to give here.
Other thing I just thought of but did not ask before, is the SQL Agent account a domain account and not Local or Local Service or something like that?
Regards, Irish
July 30, 2008 at 3:13 pm
Just a WAG - but have you tried cycling SQL Server Agent? If not, try that and see if it has any affect.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 31, 2008 at 3:47 pm
I created a similar scenario maintenance plan.
I posted part of my solution in my blog:
http://dbalink.wordpress.com/2008/07/12/how-to-create-dynamic-file-system-directory-in-tsql/
Hope that helps.
-Marlon Ribunal
SQL Server Database Administrator
July 31, 2008 at 4:13 pm
This is curious, the store procedure does not work with an IP address either
Just opening a query window in Management Studio,
Fileserver is the servername
10.0.3.103 is the IP address of the server
backups is the sharename
junk is the directory in the share
EXEC master.dbo.xp_create_subdir N ‘\\fileserver\backups\junk’ Works fine
EXEC master.dbo.xp_create_subdir N ‘\\10.0.3.103\backups\junk’
or
EXEC master.dbo.xp_create_subdir N ‘\\alias\backups\junk’ does not work
Funny though, if I create a share on the local SQL server, and go at it with IP address, it works...
I've had enuff of this game.. Use the real server name and be done with it
Thanks for everyones help
Jim
July 31, 2008 at 6:04 pm
I actually used the IP address of the server in my script:
SET @path = N‘\\[MY IP HERE]\Backup\’ + @folderdate + ‘\‘
SQL Server Database Administrator
December 12, 2011 at 1:23 pm
Not sure if you resolved this issue or not, but I had the same issue on only one server in our company, it was resolved by the advice offered on this post: http://www.sqlservercentral.com/Forums/Topic723054-5-1.aspx
Dear Sir,
The problem solved by giving Administrator in sql server configuration manager right click sql server(MSSqlserver) properties logon as Administrator Account.
It Solved....Thanks God.
Regards,
Mohammad Zubair Pasha.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply