Hi, I've had this problem a few times and solved it the same way. But it's not pretty and I was wondering if anyone knew of anything a bit more elegant... The Basic Aim: - I have a piece of code that is common through-out many Stored Procedures. - For maintenance purposes I like modularity. - So I want to put that common code in it's o...
(SQL 2005) Is it possible for a raiserror to terminate a stored proc. For example, in a large system we've got a value that wasn't expected being entered into a specific column. In an update trigger if you write: if exists (select * from inserted where testcol = 7) begin raiseerror('My Custom Error', 16, 1) end the update informati...
I have a huge ETL script that I am planning to understand. Are there any visual tools that can help me understand it more easily? I am using SQL Server 2005. ...
In SQL Server 2005, there is a feature called row_number() which makes pagination very simple. SELECT * FROM table WHERE row_number() between x and y Is there any SQL server way to do the same thing in SQL Server 2000? (Note: I don't have access to a unique sequential numbering for the query, i.e. something which would be a synonym f...
Again MSDN does not really explain in plain English the exact difference, or the information for when to choose one over the other. CHECKSUM Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indexes. BINARY_CHECKSUM Returns the binary checksu...
I want to write a non-CLR user-defined function in SQL Server 2005. This function takes an input string and returns an output string. If the input string is invalid, then I want to indicate an error to the caller. My first thought was to use RAISERROR to raise an exception. However, SQL Server does not allow this inside a UDF (though yo...
I have an application that relies very heavily on stored procedures (SQL 2005/2008). We are doing a minor update that will modify 25-35 of these stored procedures. The application is such that both versions of the stored procedure must be available. This is major version 4 of the application and usually we've been able to completely m...
I need to set a query like below: UPDATE XXXXXX IF column A = 1 then set column B = 'Y' ELSE IF column A = 2 then set column C = 'Y' ELSE IF column A = 3 then set column D = 'Y' and so on and so forth... I am able to do this using multiple queries but was wondering, if I can do it in just 1 statement instead. ...
What does Trusted = yes/no mean in Sql connection string? I am creating a connection string as below : string con= string.Format( "user id=admin;password=admin;server={0};Trusted_Connection=yes;database=dbtest;connection timeout=600", _sqlServer); Please Help ...
(WATCHDOGACIDT.COMASCALEE + WATCHDOGACIDT.COMASCALEV + WATCHDOGACIDT.COMASCALEM) AS EVM --not work (PATIENT_NAME.FIRSTNAME +' '+ PATIENT_NAME.LASTNAME) AS Fullname --work great but this code return summary ex 1 + 2 + 3 i would like return 123 but return 6 thank ...
i am having a problem with an update stored proc the error is UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. unfortuanltey, there ...
I develop a Winforms application written with framework 2.0, which has some Server Reports in Report Server (SQLServer 2005). This reports in the test enviroment are printing just right, but in production weird things happens (as ussual). Sometimes, the windows spooler sends the print job to the printer and the printer acknowledge the ...
Hi , Can any one Please tell me a query to find out all the jobs that run on particular day of a week. All the jobs are scheduled to run in Microsoft SQL Server 2005->SQL Server Management Studio->SQL Server Agent->jobs. In the properties of job and in the schedule information the days on which this job should run is given. Please he...
I am working with a table that comes from an external source, and cannot be "cleaned". There is a column which an nvarchar(20) and contains an integer about 95% of the time, but occasionally contains an alpha. I want to use something like select * from sch.tbl order by cast(shouldBeANumber as integer) but this throws an error on the...
Hi folks, I'm rewriting some old stored procedure and I've come across an unexpected performance issue when using a function instead of inline code. The function is very simple as follow: ALTER FUNCTION [dbo].[GetDateDifferenceInDays] ( @first_date SMALLDATETIME, @second_date SMALLDATETIME ) RETURNS INT AS BEGIN RETURN ABS(DA...
I finally got my SSIS package deployed to our SQL 2005 server, and I can run it from my ASP.NET 2.0 code. I needed to change my package after I first set it up. I double-clicked the .manifest file and deployed the package to the same server, but the job is never updated. It stays in the same state as when I first deployed it. Is ther...
Goal: When everybody else does SELECT * FROM mytable they see one version of the table. But when a specific user does SELECT * FROM mytable they see another version of the table. I think I'm like halfway there with creating a new role and putting the single user in it. Then creating a copy of the default table with SELECT * INTO newro...
Hi, One of our customers has a newish problem: the application grinds to halt. Thread dump shows that all threads are hanging on network IO in jdbc calls. We/I have never seen these 'network IO' hangs. Typically a slow machine w/ DB problems has either a) one or two long-running queries or b) some type of lock/deadlock. In either of t...
I'm trying to create a DDL trigger for a specific table and this is the best I could come up with: CREATE TRIGGER MyTrigger ON DATABASE FOR DDL_TABLE_EVENTS AS DECLARE @EventData xml SET @EventData=EVENTDATA() IF @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)')='TABLE' AND @EventData.value('(/EVENT_INSTANCE/O...
I'm trying to programmatically access the result of executing the system stored procedure sp_spaceused. if you pass the name of an object as a parameter, the sp returns a recordset which you can read like this CREATE TABLE #TempTable ( [Table_Name] varchar(50), Row_Count int, Table_Size varchar(50), Data_Space_Used var...