I want to use a computed bit column that will be true if another column in the table is not null. What's the correct formula for this? HasLabel = computed column (bit) Label = varchar NULL The following formula does not validate - what am I missing? Formula for HasLabel = Label IS NOT NULL ...
I need to populate a table in SQL server 2005 with some objects in a particular database (stored procedures, tables, views and functions, etc) In the table I would like one line per object and the table columns will be the name of the object , the script of the object, schema and object type. I have tried to use the script below but ...
I am trying to update a column inside of a table variable based on a condition, the condition being that the ID of the table variable does not exist in a different table: DECLARE @BugRep TABLE(BugCode VARCHAR(50),DevFirstName VARCHAR(50), DevLastName VARCHAR(50), BugDate VARCHAR(20), IsValid VARCHAR(1)) UPDATE @BugRep SET IsValid =...
I have 2 tables that are similar but not the same so a union is not a possibility. I need to combine the tables bearing in mind there's about 40 columns where only 20 are common to both. Any ideas on the best approach? Table1 ActivityCategory ActivityType Nationality Language ----------------------------------------------------...
The purpose of this query is to bring back products and their prices for products on sale and the price should be from the date closest but not equal to the date passed in, essentially the most recent price available. There are not price records for every day. Something feels a little wrong about having the aggregate select statement i...
I want to generate script to assign an user account to some securables, e.g. Table:Select. How to do this? ...
I want to check if @a is different from @b in the "ansi_nulls off": set ansi_nulls off declare @a int = 1; declare @b int = null; select case when @a<>@b then 'diff' else 'equal' end '@a=@b ?' --RETURNS 'diff' but without using "set ansi_nulls off". I came up with the following, but it's quite verbose: select case when @a is ...
I'm not very experienced with SQL Server Profiler (2005, 2008). I'd like to print some info to a trace from a stored procedure. PRINT statements don't seem to output to my trace. How can I easily get the PRINT statements to output to the trace? If that's not what trace (or PRINT) is really meant for, what's a quick easy alternative? ...
According to BOL (SQL Server Books Online) on sp_dbcmptlevel, This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE Compatibility Level instead. Now, the only TSQL way...
Is there any particular reason (performance or otherwise) to use AS ahead of = when aliasing a column? My personal preference (for readability) is to use this: select alias1 = somecolumn alias2 = anothercolumn from tables etc... instead of this: select somecolumn as alias1 anothercolumn as alias2 from tables etc... Am I mi...
How to convert the datetime value to nvarchar and want to format it "Month, Year" e-g October 1st 2009 value should get converted to "October, 2009" ...
I'm trying to understand how to combine queries when one of them returns more than one record. This is an invoicing report where I want to pull in the Serial Numbers of products invoiced. I'll abbreviate the script as much as possible to clarify. Here is my script before adding the serials: SELECT ARM.fcustno AS [Cust No] , ...
Hi, I have an SQL script than runs a couple of DBBC commands. Is there a way to send the messages generated from executing these commands to a simple log file e.g "C:\log.txt"? The messages I mean are the ones that appear in the messages box at the bottom of SQL Server 2005 Management Studio after a query has executed. The reason for ...
Given a table Event containing a field called EventTime of type DateTime and that the value will contain both date and time elements, I need create a summary query which counts the number of events in each month. The resulting type of the Group By field must also be Date Time with a 0 time element and set to 1st day of the month. This ...
I am using a timetabling application called CELCAT and trying to pull out some data about when students should have been marked for reporting... This seems to be extremely difficult because of the way the adding and removing of students on registers is structured see below: studentid eventid fromdatetime additio...
I want to join to tables and get the following output Table1 TestId1 ---------- one two three four five six seven eight Table2 TestId2 ---------- fiftythree fiftyfour fiftytwo fiftyfive fiftyone I want Table3 as my output with all rows from table1 and the first rows from table2 until there are no more rows left and then they shoul...
Is there a way in SQL Server 2008 to find the table with the most rows in the database? ...
Looking for a way to get the date in the format "11/1/2009", which would be the first sunday of next month. I want to run this query after the first sunday in october to get the first sunday of the upcoming month. What is the best method to accomplish this with a T-SQL query? Thanks ...
Is it possible to achieve the following in Linq? What I am trying to get is a list of records that have a column that displays the row number starting with 1. I need to display the row number in a gridview control to denote visually to the user how many rows have been populated. Why you ask? The user needs to enter an exact number of...
I've got various databases, and what to be sure I am removing something (a view in this case) that is truly orphaned. Is the the correct SQL to be using: SELECT r.routine_name, r.routine_definition FROM INFORMATION_SCHEMA.ROUTINES r WHERE r.routine_definition LIKE '%my_view_name%' The problem with it is that these referenc...