Tag Info

Hot answers tagged

13

I would strongly advise against this route. While it seems advantageous to only have 7 stored procedures rather than 28, it will turn into a troubleshooting, tuning, and maintenance nightmare. I don't know what dbms you are using, but the advice should apply to all of them. From a troubleshooting and usage analysis standpoint you will have a hard time. ...


6

Rather than saving your procedures in user tables, why don't you just set up a SSDT database project and use version control (TFS, Git, Mercurial, SVN or whatever you find appropriate) to take care of versioning? SSDT or other tools such as Red Gate's SQL Source Control will help you in keeping track of versions and deploying your changes to your ...


6

Getting the SQL from a DDL Trigger for whatever query that is dropping this Stored Procedure will only help so much. If the query is coming from Dynamic SQL from a Stored Procedure, or from a release script, or an integration test, application code, etc, then you will likely only capture the DROP PROCEDURE ... which doesn't give much of a clue as to where ...


5

Yes there is a way The table to store the results of the trigger USE [SOME_DATABASE] GO CREATE TABLE [dbo].[ddl_objects_log]( [date] [datetime] NULL DEFAULT (getdate()), [login_name] [nvarchar](128) NULL, [nt_user_name] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [host_name] [nvarchar](128) NULL, [text] [xml] NULL ) ...


5

There are a few options that you could use for this. The first would be to just set default values within the procedure declaration to guarantee that there would never be NULL values passed in for these parameters: CREATE PROCEDURE [dbo].[sp_Example] @parameter1 char (8) = 'test', @parameter2 char (20) = 'test2'.... The second alternative to checking ...


5

Assuming you want randomish ordering of your output you can just ORDER BY NEWID(). That would jumble up the ordering of the results reasonably well. If you only need to randomize after a certain order is established then you can still use the NEWID() trick. Below are some quick examples: -- Fully random ordering SELECT * FROM dbo.Foo F INNER JOIN ...


3

You just need to use GROUP BY: SELECT mt.TeamMemberUserID ---, <all those crazy SUM expressions> FROM dbo.timesheet AS t INNER JOIN dbo.Managetasks AS mt ON t.[Task ID] = mt.TaskID -- ... <WHERE clause> ... GROUP BY mt.TeamMemberUserID; If you need the user's names, there are several ways to do this, here's one: SELECT u.UserName --, <all ...


3

At least in this example, there's an easier approach than this. Remember that the optimizer is always trying to plan the query execution in a way that the path involving least amount of work necessary to retrieve a valid result set will be the path chosen. SELECT * FROM t1 WHERE (col1 = 'foo') OR (1 = 1); The server will always return all of the rows, ...


3

If you have default trace enabled and it has not rolled over, then you can use it to find who created the SP /* Object Altered Object Created Object Deleted */ SELECT TE.name , v.subclass_name , DB_NAME(t.DatabaseId) AS DBName , T.NTDomainName , t.NTUserName , t.HostName , ...


2

I'm coming in late, but I "do" security and *administration things. Is this the job, of a dba? to know who created what inside the sql? YES. It is important to have or create these logs wherever possible. However, it's also your responsibility, in my view, to not "open this box" until it becomes important. In other words--again, in my view--it is your ...


2

You can use sys.sql_modules (MSDN): select top 10 * from sys.sql_modules as m inner join sys.procedures as p on m.object_id = p.object_id where p.name ... It is better to alter them because you won't have to worry about existing permissions. If you drop and create the procedure, you have to set them back. DO: if not exists, create empty dummy SP and ...


1

Out of the box answer: mysql> SELECT @@version REGEXP '^5.[6789]'; +------------------------------+ | @@version REGEXP '^5.[6789]' | +------------------------------+ | 1 | +------------------------------+ (Granted, it won't work forever.)


1

Well, there's bad news, good news with a catch, and some really good news. The bad news T-SQL objects execute in the database where they reside. There are two (not very useful) exceptions: stored procedures with names prefixed with sp_ and that exist in the [master] database (not a great option: one DB at a time, adding something to [master], possibly ...


1

Array is not supported in stored procedure, you can use temporary table. Try this code: CREATE PROCEDURE `getAttritionData`(IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT) DETERMINISTIC BEGIN DECLARE i INTEGER DEFAULT 0; DECLARE CNT_EMP INTEGER DEFAULT 0; DECLARE CNT_EMP_SUS INTEGER ...


1

You have to use dbname.schema.objectName e.g. dbname.dbo.MYTABLE in your tsql EXEC msdb.dbo.sp_send_dbmail @recipients='MYMAIL', @body='100 Daily documents downloaded', @subject ='100 Daily documents downloaded from user', @query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100''' EDIT: You only want ...


1

I notice you tagged the question netezza but I'm not sure that SQLCMD (suggested in the accepted answer) works with Netezza or just with SQL Server. An alternative is to use Aginity Workbench for Netezza which provides an unattended commandline option: "C:\Program Files (x86)\Aginity\Aginity Workbench for PureData System for ...



Only top voted, non community-wiki answers of a minimum length are eligible