SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Save at client side problem.
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/16/2006 :  07:24:13  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message
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
30282 Posts

Posted - 10/16/2006 :  07:30:41  Show Profile  Visit SwePeso's Homepage
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

India
137 Posts

Posted - 10/16/2006 :  07:33:07  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message
Helsingborg
this is what i dont need the UNC path. any other solution ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 10/16/2006 :  07:37:16  Show Profile  Visit SwePeso's Homepage
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

India
137 Posts

Posted - 10/16/2006 :  07:39:14  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message
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

Sweden
30282 Posts

Posted - 10/16/2006 :  07:50:54  Show Profile  Visit SwePeso's Homepage
Add the appropriate rights?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/16/2006 :  07:55:45  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 10/16/2006 :  08:11:31  Show Profile  Visit SwePeso's Homepage
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

India
137 Posts

Posted - 10/16/2006 :  08:23:54  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message
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

Sweden
30282 Posts

Posted - 10/16/2006 :  08:26:57  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 10/16/2006 :  08:35:36  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message
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

Sweden
30282 Posts

Posted - 10/16/2006 :  08:47:59  Show Profile  Visit SwePeso's Homepage
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

India
137 Posts

Posted - 10/17/2006 :  01:12:31  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000