It is a bit of a "chicken or egg" kind of query, but can someone dreamup a query that can return the name of the current database in which the query executes? Believe me when I say I understand the paradox: why do you need to know the name of the database if you're already connected to execute the query? Auditing in a multi-database envi...
I'm wondering if updating statistics has helped you before and how did you know to update them? ...
In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'? Duplicated here. ...
I have a table that holds only two columns - a ListID and PersonID. When a person is merged with another in the system, I was to update all references from the "source" person to be references to the "destination" person. Ideally, I would like to call something simple like UPDATE MailingListSubscription SET PersonID = @DestPerson WHER...
I'd like to just put in a comment in the block of my if-statement, but I get an error when I try. I want to be more like Steve McConnell. declare @ConstraintName varchar(255) set @ConstraintName = 'PK_Whatever' IF LEFT(@ConstraintName, 2) = 'PK' BEGIN --can't drop primary keys END The error I get is: Incorrect syntax near 'END'....
Sorry if the title is poorly descriptive, but I can't do better right now =( So, I have this master-detail scheme, with the detail being a tree structure (one to many self relation) with n levels (on SQLServer 2005) I need to copy a detail structure from one master to the another using a stored procedure, by passing the source master i...
I have a report in SSRS 2005 that's based on a query that's similar to this one: SELECT * FROM MyTable (NOLOCK) WHERE col1 = 'ABC' AND col2 LIKE '%XYZ%' I need to be able to dynamically include the AND part of the WHERE clause in the query based on whether the user has checked a checkbox. Basically, this is a dynamic SQL statement a...
SQL Server 2000 Standard, Windows 2003 My coworker removed 'BUILTIN\Administrators' group from SQL Server which results in 'SQL Server Agent' not working. All my TSQLs to synchronize databases stopped working. I have Administrator rights on the server and my database user is in sysadmin role. Does any one have idea how to bring 'BUIL...
Hi all. I have mixed data i nvarchar column (words and numbers). Which is fastest way to sort data in this column in Numeric Order. Result example: 1 2 3 ... 10 11 ... aaaa aaab b ba ba ... ...
How to get the relative path in t sql? Take for example a .sql file is located in the folder D:\temp, I want to get path of the file hello.txt in the folder D:\temp\App_Data. How to use the relative path reference? Let's say I am executing the sql file inside the SQL server management studio. ...
I'm trying to test if a given default constraint exists. I don't want to use the sysbojects table, but the more standard INFORMATION_SCHEMA. I've used this to check for tables and primary key constraints before, but I don't see default constraints anywhere. Are they not there? (I'm using MS SQL Server 2000). EDIT: I'm looking to get b...
I have a T-SQL table variable (not a table) which has an auto incrementing identity column. I want to clear all data from this variable and reset the identity column value to 1. How can this be done? ...
I want to generate some XML in a stored procedure based on data in a table. The following insert allows me to add many nodes but they have to be hard-coded or use variables (sql:variable): SET @MyXml.modify(' insert <myNode> {sql:variable("@MyVariable")} </myNode> into (/root[1]) ') So I coul...
I'm not sure if this is something I should do in T-SQL or not, and I'm pretty sure using the word 'iterate' was wrong in this context, since you should never iterate anything in sql. It should be a set based operation, correct? Anyway, here's the scenario: I have a stored proc that returns many uniqueidentifiers (single column results)....
This is an issue that I've spent hours researching in the past. It seems to me to be something that should have been addressed by modern RDBMS solutions but as yet I have not found anything that really addresses what I see to be an incredibly common need in any Web or Windows application with a database back-end. I speak of dynamic sor...
This question comes close to what I need, but my scenario is slightly different. The source table and destination table are the same and the primary key is a uniqueidentifier (guid). When I try this: insert into MyTable select * from MyTable where uniqueId = @Id; I obviously get a primary key constraint violation, since I'm attemp...
I have several stored procedures in my database that are used to load data from a datamart that is housed in a separate database. These procedures are, generally, in the form: CREATE PROCEDURE load_stuff WITH EXECUTE AS OWNER AS INSERT INTO my_db.dbo.report_table ( column_a ) SELECT column_b FROM data_mart.dbo.source_table WHERE ...
I was recently tasked with debugging a strange problem within an e-commerce application. After an application upgrade the site started to hang from time to time and I was sent in to debug. After checking the event log I found that the SQL-server wrote ~200 000 events in a couple of minutes with the message saying that a constraint had fa...
I'm a newbie when it comes to SQL. When creating a stored procedure with parameters as such: @executed bit, @failure bit, @success bit, @testID int, @time float = 0, @name varchar(200) = '', @description varchar(200) = '', @executionDateTime nvarchar(max) = '', @message nvarchar(max) = '' This is the correct f...
I have a test environment for a database that I want to reload with new data at the start of a testing cycle. I am not interested in rebuilding the entire database- just simply "re-setting" the data. What is the best way to remove all the data from all the tables using TSQL? Are there system stored procedures, views, etc. that can be u...