SQL Server 2008 Unique Column that is Case Sensitive.
Is there a way to make a column both UNIQUE and Case Sensitive? I want to be able to put abcde and ABCDE in a unique column. ...
tsql
How to get around UDF restriction on side-effecting operators?
Microsoft Books Online (BOL) on Using Change Data explains a misleading error messages for cdc.fn_cdc_get_all_changes_* & cdc.fn_cdc_get_net_changes_* when an invalid, out-of-range LSN (Log Sequence Number) has been passed to them. Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure ...
Anyway to create a SQL Server DDL trigger for "SELECT" statements?
I am dealing with some sensitive Accounting tables and I would like to audit any SELECT statement executed on the table or any views associated with them. I did not find any DDL Events on BOL (Books Online) that had anything to do with SELECT statement. And DML triggers are for INSERT, UPDATE and DELETE only. Is it possible to log who ...
What is the difference between ";" and "GO" in TSQL (SQL 2008) ?
I use ADO.NET or the sqlcmd utility to send sql scripts to SQL 2008. What is the difference between using ";" and "GO" to separate chunks of SQL? Thanks, NEstor ...
Sql Server: storing text and keeping newlines?
Hi, I suppose this question is kinda easy to answer, but I can't seem to figure it out. I'm trying to store text (email bodies) into a nvarchar(max) column, but when I go read it, the newlines are all gone. At first I thought this might be some encoding/collation problem, but it doesn't seem to be the case. I've searched around and ha...
Log of Queries executed on SQL Server
i ran a t-sql query on SQL Server 2005 DB it was suppose to run for 4 days , but when i checked after 4 days i found the machine was abruptly closed. So i want to trace what happened with the query or in other words whats was the status of the query when machine went down Is there a mechanism to check this ...
SQL Server 2000 DTS - Cannot resolve collation conflict for equal to operation.
I have a SQL Server 2000 DTS package. One of the steps of this package has the following SQL: SELECT * FROM [Crocus_Limited$OrderRequestDetail] WHERE (rep_updated > GETDATE() -2) AND NOT EXISTS (SELECT OrderID FROM NavisionUpgrade.navision4.dbo.[WEBOrderDetails] rd WHERE rd.OrderID = [Crocus_Limited$OrderRequestDetail].OrderID ...
How do I convert this T-SQL query to use an explicit join syntax?
I have the following Select statement, but want to change it to use iner joins as I believe they are more efficient, but not too sure where to start. DECLARE @myNameID int DECLARE @myAddressID int DECLARE @myFirstName nvarchar(256) SET @myNameID = 1 SET @myAddressID =1 SET @myFirstName='Nathan' SELECT @myNameID = myNameID FROM ...
SQL Server: Optional variable in a stored procedure
I would like to know if there is anyway I can set one of my stored procedure parameter as optional. IF @thing_id <> '' BEGIN SET @sFiltre = @sFiltre + ' AND OPERES.OPE_THING = ' + CONVERT(VARCHAR,@thing_id) END ...
creating a table if it doesn't exist
Given the following: if object_id('MyTable') is null create table MyTable( myColumn int ) Is it not possible that two separate callers could both evaluate object_id('MyTable') as null and so both attempt to create the table. Obviously one of the two callers in that scenario would fail, but ideally no caller should fail, rather one sh...
Ad Hoc query assigns results to local variables
Hi is it possible for me to do this somehow? When i run the statement i get an exception, @Price_Plan is not delared, so obviously the adhoc query does not have scope to access @Price_Plan. Is there a workaround, or a better way to query a table whose name changes per execution of this query. DECLARE @Price_Plan varchar(3), @MNP_Network...
A single sql query which can handle both null or valued date range in sql server
Using SQL Server 2008. I have a stored proc which has start and end date as input parameters for date range. Looking for a single sql query which has a between start and end date in the where clause which can handle both cases where the dates are either both null or both have values. I don't want to use an IF statement. ...
How can I select the first day of a month in SQL ?
I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code : select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) AS VARCHAR(2)) + '/01' AS DATETIME) but this is not very elegant, and probably not very fast either. Is there a 'better way t...
Regular expression for removing quotes from quoted numbers in a string
Let's say I have a bunch of text like this (simplified example, but you get the idea): INSERT stuff(a,b,c) VALUES('1','a','1'); INSERT stuff(a,b,c) VALUES('2','b','1'); INSERT stuff(a,b,c) VALUES('3','c','2'); INSERT stuff(a,b,c) VALUES('4','d','2'); INSERT stuff(a,b,c) VALUES('5','e','3'); INSERT stuff(a,b,c) VA...
Retrieve first value with Xquery using a wildcard
In an XmlData column in SQL Server 2008 that has no schema assigned to it, how can I pull the first item at a particular node level? For example, I have: SELECT XmlData.value('//*/*[1]','NVARCHAR(6)') FROM table where XmlData.Exist('//*/*[1]') = 1 I assume this does not work because if there are multiple nodes with different names ...
How to get procedure text before ALTER from DDL trigger
I am creating a trigger to track how procedure text has been ALTERed. Inside a database DDL trigger, it's possible to access current procedure Text through /EVENT_INSTANCE/TSQLCommand. Even after investigating EVENTDATA(), it did not contain values for previous definition of procedure before ALTER. Is there a way to retrieve previous ...
SQL Server query - Selecting COUNT(*) with DISTINCT
In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, and program_name and push_number along with some other columns. GOAL: Count all the DISTINCT program names by program type and push number What I have so far is: SELECT DISTINCT COUNT(*...
T-SQL Scripts to copy all table constraints
I have created many tables on my local database and moved them to production database. Now I am working on fine tuning the database and created many constraints on my local database tables such as PK, FK, Default Values, Indexes etc. etc. Now I would like to copy only these constraints to production database. Is there a way to do it...
SQL SERVER Procedure Inconsistent Performance
I am working on a SQL Job which involves 5 procs, a few while loops and a lot of Inserts and Updates. This job processes around 75000 records. Now, the job works fine for 10000/20000 records with speed of around 500/min. After around 20000 records, execution just dies. It loads around 3000 records every 30 mins and stays at same speed...
SQL query in SQL SERVER 2005 - Comparing Dates
I'm having a hard time doing this query. I want to compare dates in my query, dates from my DB are in this format: (MM/DD/YYYY HH:MM:SS AM) I want to compare this date with tomorrow's day, today plus one. My questions are: How do I declare tomorrow's date in sql server? How would you compare these two dates? Thank you!! =D EDIT : DATE...