I want to add 1 year to a datetime-type column in every single row in a table. Adding using an UPDATE statement is easy for numeric types. ex: UPDATE TABLE SET NUMBERCOLUMN = NUMBERCOLUMN + 1 I'd like to do the same thing with a DATETIME-type... UPDATE Procrastination SET DropDeadDueDate = DropDeadDueDate + ? ...but I'm not sure wh...
I have an absolutely baffling case in front of me. I have two database environments, A and B, where environment B has been created from a backup of A. I'm running a rather simple query: SELECT customers.customerName (* varchar(100), not null *) ,countries.countryName (* varchar(100), not null *) ,Balance ...
Hello, In SQL Server 2005 I am trying to query a varchar(MAX) column which has some rows with text data that exceed the 8192. Yet, In Management Studio I have under Tools --> Options --> Query Results --> Results to Text --> Max numbers of characters displayed in each column = 8192, which is a maximum. Accordingly, it seems the trunca...
I have a temp table, that isn't going away. I want to see what is in the table to determine what perhaps bad data might be in there. How can I view the data in the temp table? I can see it in tempdb. I ran SELECT * FROM tempdb.dbo.sysobjects WHERE Name LIKE '#Return_Records%' To get the name of the table. I can see it's columns ...
I have a table with 6 million + records, but the first field has a " at beginning and the last field has " at the end i guess when doing a bulk insert they forgot to remove it. I want to run an update query which will remove the " from those 2 fields Sample data - Field 1 "18157142 "18157152 "18157159 "18157177 "18157189 "181571...
I have some book keeping tasks (reset high water marks, clear some staged data) that need to be done after each restore of a QA database. I know that I can create triggers on databases in SQL but I do not seem to be able to find a way to do it on a database restore. Since I work on a team of people with shared ownership of the database...
I have all my SQL stored in source control in the following structure Database Tables Stored Procs Views Static Data I'd like to tie my source control into SSMS, which seemingly supports source control, but SSMS wants to put all the scripts into one folder, which is a non-starter for me. Is it possible to get SSMS to ...
Hi, I have a co-worker who is working on a table with an 'amount' column. They would like to get the top 5 amounts and the sum of the amounts in the same query. I know you could do this: SELECT TOP 5 amount FROM table UNION SELECT SUM(amount) FROM table ORDER BY amount DESC But this produces results like this: 1000 (sum) 100 7...
Hi Everyone, I'm looking for some decent examples/samples using SSIS to do some ETL from one SQL Server database to another not necessarily within the same instance. The idea is to migrate rows of data with their heirarchies (relationships) from one OLTP database to another. There are some advantages SSIS offers us which makes it a go...
I have a nightly SQL Server job, scheduled on SQL Agent. It uses a number of databases, stored procedures and tables. What is the best to get a list of these? (Its SQL Server 2000) ...
Hi I am using Excel as the front end to a SQL Server Analysis Services (SSAS 2008) cube. I have a "calendar" dimension, which consists of a hierarchy of year-quarter-period where period is a 4 or 5 week month. Excel offers lots of useful options under its "Date Filters" menu such as being able to select just Quarter 1 etc. This works ...
Deletes on sql server are sometimes slow and I've been often in need to optimize them in order to diminish the needed time. I've been googleing a bit looking for tips on how to do that, and I've found diverse suggestions. I'd like to know your favorite and most effective techinques to tame the delete beast, and how and why they work. un...
In regard to static data table design. Having static data in tables like shown: Currencies (Code, Name). Row example: USD, United States Dollar Countries (Code, Name). Row example: DE, Germany XXXObjectType (Code, Name, ... additional attributes) ... does it make sense to have another (INTEGER) column as a Primary Key so that all For...
In some SQL dialects, you can state (something as): SELECT * FROM SomeTable WHERE (val1,val2) IN (SELECT val1,val2 FROM SomeOtherTable) But I don't know how to do that in the TSQL (sql server 2k) I am using. I am aware of (and using for now) workarounds like using joins or concatenated values, but is there some syntax in TSQL I...
Hi I am writing a large stored procedure, which creates a dynamic report table, of n columns in size, the first 6 are constant the remainder depend on a few arguments passed to the procedure to create the table with the required columns. The problem that I am having is with the following TSQL DECLARE @columnname VARCHAR(50) SET @column...
Quick question: is the 4GB limitation on EACH database or for the installed instance of SQL Server? As you know you can create more than one DB in an instance of SQL Server... ...
Hi, I have a report that use a multi-value parameter into a "in" statement in a query. For example (with @areas as the multi-value parameter): select * from regions where areas in (@areas) It works perfectly but now I need to send the same parameter to a function in the SQL Server 2005 database: select name, myFunction(@areas) from r...
Hi, I have created a program that allows me to search for keywords inside the code of stored procedures, functions, tables etc. in SQL Server generated text files. I am looking for a way to find out where a search term (i.e "ADMIN.TABLE) is being referenced in all the code files. I use LINQ to find references. For example when I search f...
I can write something like this with LINQ: var selection = from person in personList let initials = person.FirstName[0] + person.LastName[0] select initials; Can I do something similar with SQL, like maybe: SELECT @Initials FROM [Person] SET @Initials = SUBSTRING (Person.FirstName, 1, 1) + SUBSTRING (P...
I have an application that currently uses SQL Compact Edition as its database for capturing realtime data. Some of my users would like to write their own applications to query and report against this data while it's being captured by my application. However, SQL Compact edition runs in-process with my application and therefore does not...