Scenario: A stored procedure receives from code a DateTime with, let's say DateTime.Now value, as a datetime parameter. The stored procedure needs to store only the date part of the datetime on the row, but preserving all date related arithmetics for, to say, do searches over time intervals and doing reports based on dates. I know the...
I have an ETL process that involves a stored procedure that makes heavy use of SELECT INTO statements (minimally logged and therefore faster as they generate less log traffic). Of the batch of work that takes place in one particular stored the stored procedure several of the most expensive operations are eager spools that appear to just...
I have a table in my database the records start and stop times for a specific task. Here is a sample of the data: Start Stop 9/15/2008 5:59:46 PM 9/15/2008 6:26:28 PM 9/15/2008 6:30:45 PM 9/15/2008 6:40:49 PM 9/16/2008 8:30:45 PM 9/15/2008 9:20:29 PM 9/16/2008 12:30:45 PM 12/31/9999 12:0...
In a SQL server database, I have a table which contains a TEXT field which is set to allow NULLs. I need to change this to not allow NULLs. I can do this no problem via Enterprise Manager, but when I try to run the following script, alter table dbo.[EventLog] Alter column [Message] text Not null, I get an error: Cannot alter column 'Err...
I'm trying to write a stored procedure to select employees who have birthdays that are upcoming. SELECT * FROM Employees WHERE Birthday > @Today AND Birthday < @Today + @NumDays This will not work because the birth year is part of Birthday, so if my birthday was '09-18-1983' that will not fall between '09-18-2008' and '09-25-2008'. ...
I'm about to inherit a set of large and complex set of stored procedures that do monthly processing on very large sets of data. We are in the process of debugging them so they match the original process which was written in VB6. The reason they decided to re write them in t-sql is because the vb process takes days and this new process t...
In SQL Server, how do I "floor" a DATETIME to the second/minute/hour/day/year? Let's say that I have a date of 2008-09-17 12:56:53.430, then the output of flooring should be: Year: 2008-01-01 00:00:00.000 Month: 2008-09-01 00:00:00.000 Day: 2008-09-17 00:00:00.000 Hour: 2008-09-17 12:00:00.000 Minute: 2008-09-17 12:56:00.000 Second: ...
I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL. Any ideas? Results would be something like: TableName, ColumnName ...
Is it possible to use an IF clause within a WHERE clause in MS SQL? Example: WHERE IF IsNumeric(@OrderNumber) = 1 OrderNumber = @OrderNumber ELSE OrderNumber LIKE '%' + @OrderNumber + '%' ...
I'd like know what people think about using RAISERROR in stored procedures to pass back user messages (i.e. business related messages, not error messages) to the application. Some of the senior developers in my firm have been using this method and catching the SqlException in our C# code to pick up the messages and display them to the...
Would the following sql remove also the index or does it have to be removed seperately? CREATE TABLE #Tbl (field int) CREATE NONCLUSTERED INDEX idx ON #Tbl (field) DROP TABLE #Tbl ...
I am working on a SQL query that reads from a SQLServer database to produce an extract file. One of the requirements to remove the leading zeroes from a particular field, which is a simple VARCHAR(10) field. So, for example, if the field contains '00001A', the SELECT statement needs to return the data as '1A'. Is there a way in SQL to ...
I have created a foreign key (in mssql / tsql) by: alter table company add CountryID varchar(3); alter table company add constraint Company_CountryID_FK foreign key(CountryID) references Country; I then run this query: alter table company drop column CountryID; and I get this error: Msg 5074, Level 16, State 4, Line 2 The o...
Hi, I've got a stored procedure in my database, that looks like this ALTER PROCEDURE [dbo].[GetCountingAnalysisResults] @RespondentFilters varchar AS BEGIN @RespondentFilters = '''8ec94bed-fed6-4627-8d45-21619331d82a, 114c61f2-8935-4755-b4e9-4a598a51cc7f''' DECLARE @SQL nvarchar(600) SET @SQL = 'SELECT * FROM Answer ...
Does anyone know how Sql server determines the order triggers (of same type, i.e. before triggers) are executed. And is there anyway of changing this so that I can specify the order I want. If not, why not. Thanks. ...
I have a couple of triggers on a table that I want to keep separate and would like to priortize them. I could have just one trigger and do the logic there, but I was wondering if there was an easier/logical way of accomplishing this of having it in a pre-defined order. ...
Here's something I haven't been able to fix, and I've looked everywhere. Perhaps someone here will know! I have a table called dandb_raw, with three columns in particular: dunsId (PK), name, and searchName. I also have a trigger that acts on this table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dandb_raw_searchNa...
The name of a temporary table such as #t1 can be determined using SELECT @TableName = [Name] FROM tempdb.sys.tables WHERE [Object_ID] = object_id('tempDB.dbo.#t1') How can I find the name of a table valued variable, i.e. one declared by declare @t2 as table ( a int) the purpose is to be able to get meta-information about the ...
Simple question, how do you list all the primary keys of a table with T-SQL? I know how to get indexes on a table, but can't remember PK's. ...
I need to import a large CSV file into an SQL server. I'm using this : BULK INSERT CSVTest FROM 'c:\csvfile.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO problem is all my fields are surrounded by quotes (" ") so a row actually looks like : "1","","2","","so...