1> SELECT DISTINCT s.id, s.new, s.modified, s.deleted, s.archived, s.published, s.approved, s.submitted, s.checkedOut, s.aclId, s.parentId, s.ordering, s.templa te, s.publishVersion, w.className, w.version, w.name, w.description, w.summary, w.author, w.date, w.related FROM cms_content_stat...more >>

I have created a view for reporting. I’m basically just joining a few tables. It is for a University so the results shows students names and the credits they are currently taking and the school code (There is 3 Colleges under one ownership) The problem is some students attend two colleges and...more >>

I need a function that does the following: SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z', 'The Letter Z', ....) Where if the input value is 'X' then 'The Letter X' is returned and so on. I could write a function for this of course but I was hoping one exists in SQL ...more >>

Joe, Kudos to you on a job well done. Will be adding your 2005 edition to my personal SQL book library as well as a recommended SQL book for others. Jerry ...more >>

All I have a table of members and I would like to determine how long each member has been a member based upon the current date. I would like the result retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any assistance would be appreciated. CREATE TABLE member ( M...more >>

hi all, In sql 2000, how can you find out the exact user name who owns/created a db object? They are generally recorded as 'dbo' in the sysobjects table, how can we find out the specific user login name behind the 'dbo' entry? many thanks, JJ...more >>

Is there any difference in performance between a stored procedure that uses a view and a stored procedure that includes the full select statement defined by the view. Simple example. A real example would involve a view with more joins, etc. View: viewEmployees select * from tblEmployee as...more >>

hi, This is a sql 2000 question: Is there a way to figure out when was the last time a databse object got modified (data update, code update, object property update, etc) or accessed by users? It's not in the sysobjects table in user databases. many thanks, JJ...more >>

Hi All, I need help figuring out a query. I have two tables: REQ and PO The schema here is that there can be multiple PO rows per a single REQ row. example: REQ PO -------------- Requistion1 PO1 PO2 PO3 I am creating a query ...more >>

Here are a few gems I just found in out JobTitle table, which stores contact job titles in our database. If we have a job title, it's because it came in a bulk-insert data file from a client. The best the big dawg the boss the man The Master Himself THE ONLY PERSON IN IT DEPARTMENT The...more >>

Hello, If I cause a Rollback with a trigger, does this cause the outermost trasaction to abort or is flow of control continued? Or is an error batch aborted? Thanks, Rich...more >>

Hi - I'm no SQL wizard (obviously). I have a table (conceivably very large (500k+rows)) with a non-unique secondary index. I need an efficient query to check for row existence using that secondary index. Any ideas will be appreciated... Thanks, Bryan...more >>

Team, I have an IIS log table in SQL Server2000. The table has about 20,000 rows. I need to query the table to find how many times a unique IP appeard in the list based on date and target. The table INETLOG that has fields like USER-IP DATE TARGET I want to know how man...more >>

if i declare a variable within a stored procedure as a varchar, then later cast it to an integer- will it act as an integer for all subsequent references after the cast? example: declare @myVar varchar(2) set @myVar = '2' set @myVar = cast(@myVar as integer) select @m...more >>

Hi Freinds, SQL2000 Can I run an external dll inside the trigger? Thanks, Pat ...more >>

I'm curious if there are any bottle-neck issues I need to be aware of with an application that stores scanned images into SQL. The app uses a standard SQL Image field for the storage, and TIFF images are being stored in it. Currently my client is adding about 500 records (about 100 megs) per...more >>

I am new to SQL programming and I have a project that is for manufacturing facility. At the start of this project parts wer initially all loaded into a table (tblItems), and then parts wer removed and added as time progressed So, now they want 7000 parts added back from the backup tha origina...more >>

A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By solution: http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e select * from titles order by case when @col ...more >>

Is there any way to perform a sort within a T-SQL function? The function takes numerous "floats" as arguments and returns a single string (nvarchar) that lists those floats "float1value - float2value - float3value - etc". Is there anyway to sort those floats before passing them out via the s...more >>

Hi All ... I am looking for simple T-SQL syntax for using ExportData method with Bulkcopy object to export one table in tab delimited, text file format to C:\TableName.txt. First row of text file should be column headers. Anybody know offhand? [TableName].ExportData ??? Thanks for yo...more >>

Just in general terms (I will look up the specifics), how would I go about doing this? There is value.csv list containing values (000, 001, 002, 003, etc . . ..). There is a table named Attrib. Each value in value.csv needs an Insert Into Attrib. Basically I want to perform the following...more >>

Hi there. I am trying to write a single script to create some stored procedures. One of the stored procedures however, refers to a database which may or may not be present on the server. In the case of that database NOT being present, I would like to create the stored procedure with differe...more >>

I have a datagrid control bound to a dataview of a table in a dataset. I am using a tablestyle to control which of the table columns are displayed in the datagrid. When any cell in a row is selected I want to select and highlight the entire row. By catching the mousedown event and using HitTest ...more >>

I'm pretty good with T-SQL. We are using SQL Server 2000 and this will be retrieved in Access 2000 via ADO. The problem is that I have a query: select SUBSTRING(VANR,3,4) AS STYLE, CASE WHEN VERA='E' THEN VMGL ELSE VMGS END AS BOOKED, CASE WHEN VDAT>=20030929 AND VDAT<=20040926 THEN CASE W...more >>

Hi: Does any one know where I can find a script that will change ownership of all database objects at once. I took over control of a large database and changing owners using sp_changeobjectowner one at a time will take forever. Thanks, Charlie ...more >>

hello, in a table with 2 fields (department and sales) i have to calculate the sales for the company and the sales excluded spme departments. example original table OnlyDepA 100 OnlyDepB 200 OnlyDepC 500 ............. AllDep. 10000 What i need to calculate What are the sales...more >>

We are writing this: CREATE FUNCTION TestF() RETURNS varchar(100) AS BEGIN declare @Stock varchar(100) declare @Indicator varchar(50) set @Indicator = 'tbl_stock_master' set @Script = (select fld_stock_code from tbl_stock_master as ind where ind.fld_stock_code = '12345'...more >>

I want to export 3 tables in a SQL database with the table definition and want to then import from this dump into another SQL database where these tables do not exist. The tables are - Cust, MatProd, Plant the database to be exported is - SourcePur and the database these table have to be cr...more >>

hi all, IN Sql Server: i need to create trigger on table A as when i insert new row in Table A ,new record will be inserted in Table B which has the same primary key of Table A please Help thank you ...more >>

Hi! I'm a newbe to SQLXML and SQL updategrams. I know that it is possible to delete specific rows from a database using SQL updategrams. My question is: Is it possible to delete ALL rows from a table using updategrams? In other words, I would like to express the query: "delete from TABLENA...more >>

I have a table like this Job, EventType,TimeEvent A,Start,12/10/2005 10:00 A,End, 12/10/2005 10:30 B,Start, 12/10/2005 10:31 B,End, 12/10/2005 11:45 and so on... Which is the way to have a quey result with Job, JobTime (in minutes) A, 30 B, 74 Thanks...more >>

Hi, I need to get just the date part from getdate(), eg I need to test for dDate > getdate() but the RHS should be today 12am. What's the correct function for this? Cheers, NEIL ...more >>

Hello, Can a stored proc add a record to one table, and then having retrieved the scope_identity, add a record to another table? Would the following code be appropriate? Thanks Soc. CREATE PROCEDURE [dbo].[usp_addto2tables] @a_variable1 INT, @a_variable2 INT, @a_variable3 INT, @b_...more >>

I have been looking for a thread with information on how to move a database with a file group in it. Basically I need to relocate ( due to newer disks) a database to a new set of drives. I am going to use the current disks for logs only. But back to the question I know how to use sp_dettach_db to...more >>

i got two database (A & B) with same structures, now, I need to add two fields in one table in database A , now , i need to modify with database B also, Any shortcute , and I sync their table structures ?? [both database got the data inside] ...more >>

Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue. I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode. That code will be in the form of 110012345678. 1100 wi...more >>