Problem with creating dynamic T-SQL.....

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with creating dynamic T-SQL.....

Author

Topic

sonnysingh
Starting Member

42 Posts

Posted - 2005-05-19 : 12:08:35

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

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-19 : 13:38:39

Why not code

[CODE]SELECT (dbsize*8.00/1024/1024) As MyResult
FROM drivespace
WHERE drive = @drive
AND tstamp = getdate() -- This won't match... EVER ![/CODE]

If you want to check for tstamp being today, don't compare the hours, minutes, seconds, milliseconds as you have above.

Try

AND DATEDIFF(dd, tstamp, GETDATE()) = 0

sonnysingh
Starting Member

42 Posts

Posted - 2005-05-19 : 14:01:28

Thanx SamC

even I took tstamp out like following T-SQL It's still given error.. I know there is mistake somewhere in T-SQL ..but can't figure out
here it is error msg.. at the bottom..

DECLARE @dbsize dec(15,2)
DECLARE @SpaceReq dec(15,2)
DECLARE @drive char(1)
DECLARE @DriveFreeSpace dec(15,2)

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...

Here is another statement.. I am having problem with..
EXEC('SELECT @DriveFreeSpace = (MBFree/1024.0), @SpaceReq = (@dbSize*3), @DriveNme = drive
from UT_DiskSpace where drive = ' +''''+@drive+'''')

Please help..
Thanx

SqlIndia

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-19 : 14:45:03

Too many right parens on line 76 probably.

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-05-20 : 21:03:23

Here you go.


DECLARE @drive char(1)
DECLARE @dbsize DECIMAL(15,2)
DECLARE @dbSizecmd VARCHAR(255), @Parm NVARCHAR(1000), @sql varchar(1000)
SET @sql = 'SELECT @dbsizecmd = (dbsize*8.00/1024/1024) FROM drivespace where drive = ' + @drive + ' and tstamp = getdate()'
SET @Parm = '@dbsizecmd DECIMAL(15, 2) OUTPUT'
EXEC sp_executesql
	@sql,
	@Parm,
	@dbsizecmd OUTPUT
SELECT @dbsizecmd

Mike Petanovitch

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-20 : 22:39:52

Why are you using Exec() ??? What is wrong with regular SELECT/SET statements?

- Jeff

sonnysingh
Starting Member

42 Posts

Posted - 2005-05-22 : 11:40:36

Hi Guys..

Thnx a lot for your replies.

mpetanovitch.. your query worked fine..

jsmith8858.. you are right i should use simple select or set..(but i didn't know how)..But I suppose that's why this site for,learn by sharing knowledge and help each other..
any way actually I have solved this using simple way..
like this (some example)....

Update UT_diskspace SET dbname = (select database_name from #database_Info) where drive = @drive

Update UT_diskspace SET dbsize = (select dbsize from #database_Info) where drive = @drive --and tstamp = getdate()

select @dbSize = (dbsize*8.00/(1024*1024)) from UT_diskspace where drive = @drive

and works perfect..

Thanks for suggestion guys.
regards

SqlIndia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources