| Author |
 Topic  |
|
|
gsgill76
Posting Yak Master
India
137 Posts |
 Posted - 10/16/2006 : 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 |
Edited by - gsgill76 on 10/16/2006 07:29:47
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 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 Larsson
Helsingborg, Sweden |
 |
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/16/2006 : 07:33:07
|
Helsingborg
this is what i dont need the UNC path. any other solution ?
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 07:37:16
|
I don't even think a mapped network drive on sql server machine will work for you?
Peter Larsson
Helsingborg, Sweden |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/16/2006 : 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
Sweden
29138 Posts |
Posted - 10/16/2006 : 07:50:54
|
Add the appropriate rights?
Peter Larsson
Helsingborg, Sweden |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/16/2006 : 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.
|
Edited by - gsgill76 on 10/16/2006 08:03:46 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/16/2006 : 08:11:31
|
I meant rights for the SQL Server account to access the local path.
Peter Larsson
Helsingborg, Sweden |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/16/2006 : 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
Sweden
29138 Posts |
Posted - 10/16/2006 : 08:26:57
|
UNC PATHS are for shares, not physical drives.
SET @sPath= '\\vsnet1\\C:\newww' is wrong
1) Share the folder with an unique name on the machine.
2) SET @sPath= '\\vsnet1\shareduniquefoldername' or
SET @sPath= '\\vsnet1\c$\newww'
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/16/2006 08:28:27 |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/16/2006 : 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
Sweden
29138 Posts |
Posted - 10/16/2006 : 08:47:59
|
You can store each and every users path in a lookuptable.
select suser_sname()
Peter Larsson
Helsingborg, Sweden |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/17/2006 : 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 |
Edited by - gsgill76 on 10/17/2006 01:21:47 |
|
|
| |
Topic |
|
Save at client side problem.
Forum Locked
