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.
| Author |
Topic |
|
cwilcoxjcp
Starting Member
2 Posts |
 Posted - 2001-12-06 : 10:19:24
|
| Ok guys here is what i have to do. I have to keep track of 'counts' of assets and products that our Asset Management software tracks on a daily basis to track the changes in the amount of users or notebooks (for example) that we are using. I also have to make it where any dumb end user can put in a query and have it start tracking the new item. Here is what i have done so far and it works, but i am looking for any other ideas or suggestions that could improve it. I have 2 tables linked togethor, 1 holds the sql commands and job id, the other holds all the values that we are tracking on a daily basis.Here is the stored procedure I am currently using.CREATE Procedure [dbo].[sp_historicaldata] As Declare @SqlStatement varchar(500) declare @SQL VarChar(500) Declare @lHistid int Declare @Title varchar(50) Declare @numcount numeric Declare CommandCursor Cursor for Select lHistid,sqlstatement,title From itamHistoricalCommands For Read Only Open CommandCursor While (0=0) Begin Fetch Next From CommandCursor Into @lHistid, @SqlStatement,@Title If (@@Fetch_Status <> 0) Break exec(@SQLStatement) set @numcount = @@ROWCOUNT Set @SQL = 'Insert into itamHistoricalData (lHistid,datavalue) values(' + Cast(@lHistid as varchar(50)) + ',' + Cast(@numcount as varchar(50)) + ')' Exec (@SQL) set @SQL = 'Update itamHistoricalCommands set dtLastRan=GETDATE() where lHistid=' + Cast(@lHistid as varchar(50)) Exec (@SQL) End Close CommandCursor Deallocate CommandCursorGOI am wondering can i do this without a cursor to step through the rows that contain the many sql statements? Also right now, there is a potential security risk as someone could put in a DELETE FROM query. I want the SP to check and only exec SELECT statements and possibly email me when anyother type of query is scheduled. Let me know your thoughts..... |
|
|
cwilcoxjcp
Starting Member
2 Posts |
Posted - 2001-12-13 : 11:15:08
|
| Can someone help me please? |
 |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2001-12-13 : 12:41:43
|
| I've read this three times and I still don't quite understand what you're trying to do. You're letting "dumb" users write actual SQL statements against a database? Can't you offer some preselected queries through a UI? It's pretty easy to do this through a web-based system, crystal, Access, etc.If you are worried about delete statements, use permissions on the tables to not allow them. |
|
|
|
|
|
|
|
|
Stored procedure