May 19, 2005 at 10:20 am
Hi All
I have this prblem and i could figure our that how I will construct this T-SQL...
Start here.......................
DECLARE @drive char(1)
DECLARE @dbsize dec(15,2)
DECLARE @dbSizecmd varchar(255)
SET @drive = 'C'
SET @dbSizecmd = '(select (dbsize*8.00/1024/1024) from drivespace where drive = ' +''''+@drive+''''+ ' and tstamp = getdate())'
Execute sp_executesql @dbSizecmd, @dbsize output
End here....................................................
I want final T-SQL like this..........
SET @dbSize = (select (dbsize*8.00/1024/1024) from drivespace where drive = ' +@drive+ ' and tstamp = getdate())
Plesae any one can help...
Thanx in advance..
SqlIndia
May 19, 2005 at 11:26 am
Why do you want to use sp_executesql for this??
This query would do the same thing :
DECLARE @dbsize dec(15,2)
DECLARE @drive char(1)
SET @drive = 'C'
SET @dbSize = (select (dbsize*8.00/1024/1024) from dbo.drivespace where drive = @drive and tstamp = getdate())
Note that tstamp =getdate() might fail because getdate() returns the time portion of the day too and it's unlikely that you will have a timestamp that match getdate() to the milisecond.
you can use this little trick to return today's date with the time truncated : dateadd(D, 0, datediff(d, 0, getdate()))
May 19, 2005 at 12:04 pm
Thanx Remi
even I took date out like following T-SQL it still given error.. I know i have mistake somewhere ..here it is error msg.. at the bottom..
DECLARE @dbsize dec(15,2)
DECLARE @drive char(1)
set @drive = 'C'
EXEC('SET' +@dbSize+ '= (select (dbsize*8.00/1024/1024) from dbo.drivespace where drive = ' +''''+@drive+''''))
PRINT @dbSize
error msg....
Server: Msg 170, Level 15, State 1, Line 76
Line 76: Incorrect syntax near ')'.
and this code line is 76th line...
SqlIndia
May 19, 2005 at 12:12 pm
I'm gonna repeat my question :
WHY ARE YOU using dynamic sql for this task???? there's absolutely no need in this case.
have you tried runnning this statement in static sql?
DECLARE @dbsize dec(15,2)
DECLARE @drive char(1)
SET @drive = 'C'
SET @dbSize = (select (dbsize*8.00/1024/1024) from dbo.drivespace where drive = @drive and dateadd(D, 0, datediff(d, 0, tstamp)) = dateadd(D, 0, datediff(d, 0, getdate())))
Note that the way I coded this you might get an error that the select is returning more than 1 record if you have multiple rows for each days on a single drive.
May 19, 2005 at 1:28 pm
Hi Remi
thnx for response.. yes i have done in static sql and working fine.. but u see I am using this in store procedure and user will allow to enter two parameters to check the space on required drive and for required database...
Therefore, they have flexibility to choose drive and database, they wish. so, that's why I need to bulid it dynamic way.. u see mostly user have more than one hard drives or partitions on server.. In this way they can able to choose drive on which they wish to restore db..
I have working version for drive 'C', which I have assigned value for drive in the store procedure... I am just new for advance dynamic statement and it is about output value in the same satement...
That's why I need help in it.. so you can...
regards
SqlIndia
May 19, 2005 at 1:53 pm
first problem :
move the 2nd to last parenthesis inside the last quote.
2nd you can set the local variable with the exec statement, you'll have to lear how to use sp_exectuesql to do this but I can't help you with that.
May 19, 2005 at 7:25 pm
SqlIndia I would suggest that this funciton would work better on the client than on the server. You might want to look at the FileSystem Object in ASP,VB,C++ and Java (not sure about Java) or the FileListBox and DriveListBox functions in C++ and VB.
Once the user has picked the Drive and Db you could then construct your connection on the client.
HTH Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply