Author |
Topic |
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-16 : 07:24:13
|
Hi We are have a client-server setup for SQL Server 2005(EE). I am working on client(using connectivity tool sql server native, SSMS), with full Admin right(i mean sa user).I have a stored procedure which generates the backup of database, using XP_CMDSHELL & BCP commands. Every thing so fine. But the when BCP Generates the files it stores at server hard disk, not into at the local hard disk.( although i had given the c:\abc\ path).Same problem is there, when i restore the Database(i had too written a SP for that),it always picks the paths from the Server not from the client.How could i force to save my files at client side, i mean when user at client end executes the Backup SP, it should get the file at his System Hard disk. (As there is no rights to user to access the files at the SQL Server).I know i can use the UNC path, but any other solution?Regards,Thanks.Gurpreet S. Gill |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 07:30:41
|
All paths for BACKUP and RESTORE is from the SQL SERVERS point of view, of course.Path "C:\abc\" is at server machine. Read all about it in BOOKS ONLINE.If you want BACKUP command and RESTORE command to be able to use the path on YOUR machine, use UNC paths (found on GOOGLE or BOOKS ONLINE).Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-16 : 07:33:07
|
Helsingborgthis is what i dont need the UNC path. any other solution ? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 07:37:16
|
I don't even think a mapped network drive on sql server machine will work for you?Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-16 : 07:39:14
|
This is Right, i just try this thing but what if the Rights are not there for that client ? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 07:50:54
|
Add the appropriate rights?Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-16 : 07:55:45
|
No, we cant assign the rights to the user/client, as they are very big in number, as well as it makes a security hole too. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 08:11:31
|
I meant rights for the SQL Server account to access the local path.Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-16 : 08:23:54
|
Yes rights are there for the SQL Server account.Just check that code snippet of my SP DECLARE @sPath Varchar(100),@sMD varchar(100) SET @sPath= '\\vsnet1\\C:\newww' SET @sMD = 'MD ' + @sPath PRINT @sMD EXEC xp_cmdshell @sMD I tried to create the folder at client 'vsnet1' with name 'C:\newww'this return error as The network path was not found.NULL |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 08:26:57
|
UNC PATHS are for shares, not physical drives.SET @sPath= '\\vsnet1\\C:\newww' is wrong1) Share the folder with an unique name on the machine.2) SET @sPath= '\\vsnet1\shareduniquefoldername' orSET @sPath= '\\vsnet1\c$\newww'Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-16 : 08:35:36
|
Oh, it was my error, that UNC path is working now, but now the problem is how to find the name of the clinet + shared drive in SP(\\NameOfClient\\drive$\\folder) & main is the rights assignments? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 08:47:59
|
You can store each and every users path in a lookuptable.select suser_sname()Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-17 : 01:12:31
|
Peso,could you explain a bit more, i dont get how to get the path, which is shared to SQL Server, to store the files(or to create the folder)SELECT SUSER_SNAME()returns "sa"I dont understand hows this help me?how to get/set rights for the SQL Server account to access the local path?Kind Regards,Thanks.Gurpreet S. Gill |
|
|
|