Tag Info

New answers tagged

1

You could use a DDL trigger to print the output of the object that was affected, or you could use the trigger to automatically log the change in an audit table so that you can view DDL change history. Here is an example http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/


2

Use :on error exit and/or -b. This will cause sqlcmd to exit after the first batch that hits and error, and set the %ERRORLEVEL%: -b Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than ...


2

Check sys.objects for the row that has the right value in name and see if the modify_date column is newer than when your script started. SELECT SYSDATETIME() AS ScriptStart INTO #scriptstart; GO ALTER VIEW ... GO ALTER PROC ... GO SELECT name FROM sys.objects WHERE modify_date > (SELECT ScriptStart FROM #scriptstart); This will list all the objects ...


1

May be this example ,will help you: ALTER VIEW dbo.vTest AS SELECT TOP(10) ProductName FROM [dbo].[TestData] GO IF @@ERROR <> 0 print 'ERR' ELSE print 'ok' GO ALTER VIEW dbo.vTest AS SELECT TOP(10) PoductName FROM [dbo].[TestData] GO IF @@ERROR <> 0 print 'ERR' ELSE print 'ok' GO And instead of printing the message, ...


0

This can be a one-liner: RETURN EXISTS ( SELECT * FROM tbl WHERE ... ). You get 0 or 1, not NULL. At which point, why even create a function? The query to run without the function: SELECT EXISTS ( SELECT * FROM tbl WHERE ... )


1

Perhaps doing a COUNT is better since it must be a number always DELIMITER $$ DROP PROCEDURE IF EXISTS `USER_EXISTS` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS` (IN `GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4) BEGIN SET @User_exists = 0; SELECT COUNT(1) INTO @found FROM `dbname`.`tablename` WHERE `username` = ...


0

Large, general-purpose search queries often produce an inefficient plan. This can be ameliorated by adding OPTION (RECOMPILE) to the query to invoke parameter embedding. Now your problem is that the general-purpose query is recompiled with every execution, which is itself an overhead. Avoid this by pulling out the common sets of parameters and have ...


1

I would create One stored procedure with optional parameters. Also instead of using WHERE @Var is NULL or Column = @Var use dynamic sql to build your query. This kind of query where clause results in a very unefficient execution plan. Also Sql server doesn't do any shortcircuting when evaluating OR conditions in where clause, for example even if @Var is ...


1

Quick and dirty, but I'm assuming you'll be putting this into a more formalizing procedure for spitting out names. DECLARE @Firstname nvarchar(50) DECLARE @Lastname nvarchar(50) SET @Firstname = 'Marc-Oliver' SET @Lastname = 'Saint-Onge' SELECT @FirstName, @Lastname IF CHARINDEX('-', @FirstName) <> 0 SELECT SUBSTRING(@Firstname, 1, 1) + '.' + ...


0

Thanks Dog OnAPorch, your link was veryhelpful. i for-gone checking if the trigger exists on this one off rare occasion but i managed to get a working version from the link you provided. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER qf.Update_ServiceWorkingHours_Cache ON qf.customer_working_Hours AFTER INSERT,UPDATE AS BEGIN ...


3

I am assuming you are trying to implement some basic replication using a trigger. As long as your process remains fairly simple, this should work OK. However, I would advise you to read up on (i.e. google) why executing stored procedures from triggers is not a good idea, and also about SQL's built in transactional replication feature. Here is a practical ...


0

Here's a link that's very similar: http://stackoverflow.com/questions/3768278/call-stored-procedure-within-create-trigger-in-sql-server Since the scope is an action resulting from a data change within a table, you'll want to write the trigger on the table, not the database. When you say "i want the script to check if the trigger exist and if it doesn't ...


1

SELECT C.customername ,C.customercode ,L.[loan type] ,S.[account number] ,S.status FROM [first table] AS C INNER JOIN [second table] AS L ON C.customercode = L.customercode INNER JOIN [3rd table] AS S ON S.customercode = C.customercode This is only as a guidance; it is better to post some sample data and ...


0

SUGGESTION #1 If you login as root@localhost, you can change the definer in mysql.proc In your case, you would do UPDATE mysql.proc SET DEFINER = 'dbname@localhost'; SELECT name,type,definer FROM information_schema.routines; I am not sure if the stored procedures load automatically into INFORMATION_SCHEMA.ROUTINES If not, run these FLUSH PRIVILEGES; ...


0

In relation to the ANSI_PADDING portion of your question, you can use sys.columns From ANSI_PADDING MSDN article: Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data. ... This setting affects only the ...


1

Another possibility: Encode the values with Base64. Concatenate the values into a comma-separated string. Pass the resulting string. Split the string in the stored procedure. Decode the strings using Base64. Base64 encodes no comma in its default character set and either is or appears to be supported by: Oracle SQL Server Sysbase PostgreSQL MySQL ...


2

Pass the values as XML (in a VARCHAR). The following database all support shredding XML from the query language: Postgres MySQL SQL Server Oracle DB2 UDM Obviously, not every obscure database on the planet supports it. But the ones above are realistically the only ones you need to care about to hit 99.9% of the database market.


0

One tests for NULL by saying IS NULL, not = NULL. Nothing equals NULL. (I don't know if this is the problem, but it looks 'wrong'.


2

If a stored procedure is maxing out your CPU, you really need to tune that query to fix the root cause rather than working around it. Analyse the query plan using native SSMS or SQL Sentry Plan Explorer. Some things to look out for to improve the query plan from http://www.brentozar.com/ are ...


1

Changing ARITHABORT inside the procedure causes it to recompile: http://support.microsoft.com/kb/243586 So most likely you're dealing with parameter sniffing. If you don't figure other ways to fix this, adding "... option (recompile)" into the end of the statement you're having trouble should solve the issue. That of course increases CPU usage (slightly) ...


2

No, but given the function is read-only, one can try running it again in the concurrent session and see if the slowdown is reproducible. If it is, try running the same queries as this function does manually. You may find them by running \sf+ on the function in psql, or, if you are running Bucardo5, by looking at the schema definition: ...


0

As a very generic answer, if you want to test code of this nature without actually changing the data then it is possible to run your code, query the results, and then undo any changes before they are committed. -- Begin a new transaction begin transaction -- Show initial data select * from [dbo].[employee] -- Your statement to change the data ...


1

In addition to Kapil's answer, I'd like to add the following... SQL Server is optimized to perform set-based operations. Your stored procedure reads very much like an iterative process. (Do this step, increment counter, do another step, etc.) I believe that if you restructure your stored procedure to perform one operation on 1,000 rows as opposed to ...


1

There is a system view called pg_stat_user_functions. If track_functions has been set to all, you can simply do ... SELECT * FROM pg_stat_user_functions. self_time is the time needed by the function itself; if you subtract total_time from self_time you can see how long the function has spent calling somebody else. Some some deeper profiling is not too easy ...


0

I believe you can give a try with below 2 points as important aspect while moving ahead to improve the performance of stored procs. Use the SET NOCOUNT ON statement to prevent SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure. For example, if you have eight operations in a stored procedure and you have not used this ...


-2

This code work in C# + SQL SERVER 2008 USE [erp] GO /****** Object: StoredProcedure [dbo].[test] Script Date: 02/03/2015 12:16:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[test](@table_name varchar(max)) AS BEGIN declare @tablename varchar(max)=@table_name; declare @statement varchar(max); set @statement = ...


3

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: ...



Top 50 recent answers are included