11,453 reputation
33267
bio website
location Connecticut
age
visits member for 3 years, 7 months
seen 16 hours ago

I'm a dude.


Dec
24
revised How to empty a column in mysql
added 2 characters in body
Dec
16
awarded  Populist
Dec
15
comment Physical Table being used a Temp Table in a warehosue. Any advantage?
One advantage I can think of in large processes like ETL is being able to resume if there's an issue. Another is being able to use your actual DB files instead of tempdb for very large data sets. You need to give some more info on the size of the data you're dealing with for some context here.
Dec
9
comment GROUP BY Returning Different Totals
That's because you're using MySQL's non-deterministic grouping behavior which does not align with the SQL Standard. If you want consistent results you need to have all columns in your SELECT either be in the GROUP BY or an aggregate function.
Dec
9
comment Database structure for users, events and event lists
This is the obvious solution. As far as scalability, as long as appropriate indexes exist this should be very quick to query.
Dec
8
comment How to allow to execute stored procedures but not writes?
@gt6989b If you DENY DELETE, INSERT, UPDATE on a database or schema I believe it will only affect tables and views.
Dec
8
comment How to allow to execute stored procedures but not writes?
In that case you need to read about SQL Server permissions. There are 3 permission variants - GRANT to give permissions, DENY to deny permissions, and REVOKE to remove a GRANT or DENY. Without a GRANT or DENY the user can inherit permissions, say from getting access to a stored procedure.
Dec
8
comment How to allow to execute stored procedures but not writes?
I think you can do this with a combination of GRANT EXEC on the objects/schemas/database and DENY DELETE, INSERT, UPDATE on the underlying tables.
Dec
8
comment Stored procedure - problem with merge - Insert
I would just use an UPDATE and an INSERT...
Dec
8
comment Stored procedure - problem with merge - Insert
Why are you using MERGE for these operations if each statement only does an update or an insert?
Dec
8
comment Stored procedure - problem with merge - Insert
But there's no error message?
Dec
8
comment Stored procedure - problem with merge - Insert
Define "fails" - does it throw an error, does it just not insert the data?
Dec
5
comment Reuse a complex non-deterministic expression in SELECT and WHERE clauses
On second thought this may take care of determinism too, since the calculation is only run once per row.
Dec
5
answered Reuse a complex non-deterministic expression in SELECT and WHERE clauses
Dec
5
revised How index work in case of write operation?
edited tags
Dec
4
comment How index work in case of write operation?
You really need to specify what RDBMS you're dealing with for internals questions like this.
Dec
3
comment Run a specific SQL Server job step from a stored procedure
Actually I just read the part where you want no other steps to execute, and that's not supported by SQL Agent jobs as far as I know. However, you COULD read the command from the table I mentioned in the previous comment and run it that way.
Dec
3
comment Run a specific SQL Server job step from a stored procedure
This should be trivial using sp_start_job- see msdn.microsoft.com/en-us/library/ms186757.aspx The only tricky part will be getting the step name from the step number, which you can get from msdb.dbo.sysjobsteps
Dec
3
comment Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql
Can you please show the code that you tried?
Dec
3
awarded  Enlightened