Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Save at client side problem.

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 Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-16 : 07:33:07
Helsingborg
this is what i dont need the UNC path. any other solution ?
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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 ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 07:50:54
Add the appropriate rights?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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.
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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


Go to Top of Page

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 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
Go to Top of Page

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?


Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -