hey everyone. I need a query that will select records from a table based on the date and times. Basically, it's to do with different employee shifts, especially the ones that cross midnight. How would I construct a select statement to get all shift details that fall between the periods 2...more >>

Hi, SQL Server 2000 sp4 I have a DML trigger on a user table which can be found in systemobjects but when quering INFORMATION_SCHEMA.ROUTINES for ROUTINE_NAME LIKE '%myTrigger%' the trigget is not found. What I actually would like to know is the date when the trigger was created or ...more >>

hey all, is there a way in a select statement to get the description property for a field? thanks, rodchar...more >>

My sproc recieves an account # then it loops thru multiple databases and inserts matching records into a temp table. When this process is complete, my temp table has these fields populated. POnum, activitydate, amount, sstatus. I now wish to change the sstatus column, grouped by POnum, to the...more >>

I have a data like this: Id LastName Activity ActivityQty ---------------------------------------------------------- 1 Tiesiera Fun 2 1 Tiesiera Eat 3 1 Tiesiera Run 4 I want data to look like this. I want r...more >>

I have two tables, one contains the IP Addresses of servers that should be receiving updates (called deliveries): CREATE TABLE [dbo].[tbl_Server]( [ServerID] [int] IDENTITY(1,1) NOT NULL, [ServerIPAddress] [nvarchar](256) NOT NULL ) The other table contains the deliveries by hour, rolle...more >>

I am working with the ceiling() function and am getting unexpected results. In the BOL it indicates the ceiling function returns the smallest integer greater than, or equal to, the specified numeric expression. I was thinking all the below queries would return 2. If I run the following q...more >>

I have a logins table logins fk_users_recID = id of the user LogDate = date of logging in/out InOrOut = 0 or !0. 0 = logged out, any other value = logged in Every time someone logs in or out a 1 or 0 is written into the logins table The following query gives me a list with 2 records for ea...more >>

I have an application that is getting a few deadlock errors while running some Select statements for reports. It is running against a database that is in use by an application I didn't write. Is there any way to avoid deadlocks for simple queries? I'm using ADO against SQL Svr 2000. ...more >>

Hi, I think this should be easy, but I can't find the answer... I need to combine a column from two seperate tables. So, if Bill, Susan, Fred are 3 rows in TableA, and Cindy, Linda and Mike are 3 rows in TableB, I need a result of 6 rows (only one column) with Bill, Susan, Fred, Cindy, ...more >>

Hi I have a complex query that I need to use temp tables. it goes something like this source >> Temp1 >> temp2>>Temp3>>Temp4>Temp5>>Destination. my question is what is the preferred way to do this? I can put them in tempt tbles or Variable , and derrived tables. However, which one is the mos...more >>

I have a view across two similar tables in two seperate databses on the same server. I have noticed that the query runs faster accessing a table directly when compared to accessing a single table trhough the view. (Well defined constraints is available on the tables). But when I create a vi...more >>

CREATE table #tmp1(sName varchar(20), code varchar(1)) SELECT 'burns', 'x' UNION ALL SELECT 'burns', 'y' UNION ALL SELECT 'smith', 'x' UNION ALL SELECT 'smith', 'y' UNION ALL SELECT 'smith', 'z' UNION ALL SELECT 'jones', 'x' UNION ALL SELECT 'jones', 'y' UNION ALL SELECT 'van', 'x' UNION ...more >>

Is it possible to write select so the rowsa are selected as a column. ...more >>

I have follwoing tables: 1. Events table Column: 1. EventId(int) PK 2. EventName(varchar(36) 2. Registration table Column: 1. RegistrationId(int) PK 2. EventId(int) FK 3. RegistrationFee(money) 4. LateFee(money) 5. FirstName(varchar(36)) 6. LastName(varchar(36)) 3. EventMeal ta...more >>

Hi all, How would I capture the output of the results of the code below? I want to check the file if "The backup set is valid." or not before I restore. This will be automated. I will send an email to a few people if not valid. DECLARE @cmd VARCHAR(1000) SET @cmd = 'RESTORE VERIFYONLY FROM...more >>

Hi, I have a stored procedure call logger with some simply code create procedure logger @file varchar(100), @msg varchar(3000) AS declare @FS int, @OLEResult int, @FileID int execute @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT IF @OLEResult <> 0 raiserror...more >>

how do you set the control source of a text box on a form using a conditional statement in an adp using sql server 2005? Original code for controlsource looks like this: =IIf([nomen_dif]=True,[UNITNOMEN],"") I have tried many versions including case statements and if statements but nothing ...more >>

Hi, I wanted to know the table last modified time quickly. What shall I do? I am using MS SQL 2000. Thanks! Michael ...more >>

Hi all, I have two tables and want to matching CustEmail against our SourceEmail and return custid and email below. The SourceEMail has multiple email columns so how do I compare against each column 1 - 5. Please see the results show below. Any help would greatly APPRECATE. IF OBJEC...more >>

I am trying to build a Quarterly Report and would like to know how to build syntax to tell me what quarter that the date column is in. My date column looks like this; "2014-01-01 00:00:00.000". The column name is upr30100.Chekdate. Thanks, Ryan...more >>

Setup: I have two huge tables (table1, table2) millions or records in two databases. Both the tables have the necessary indexes, constraints etc. The tables are split based on the columns "year" constraint "< 2000" in table1, ">= 2000" in table2 I have a view(ALLDATA) as select * from table1...more >>

Trying to figure out recursive queries, but having a bit of trouble applying. I have a table with: wid - keyfield, not null parentID - int start_date - smalldatetime title -varChar(100) The structure is that if parentID=0 then it's the base of the lookup, meaning parent, but parentID co...more >>

I have a common table expression query. I can run it successfully in the management studio. However, if I copy the same query to my SqlCommand and run it, it will show an error with [System.Data.SqlClient.SqlException]{"Incorrect syntax near the keyword 'WITH'."} System.Data.SqlClient.SqlExc...more >>

Hi, I have values like 0.50000000 which I want to round up to the nearest whole number. In this case I want to round 0.50000000 to 1 select round(0.50000000, 10) How can I do this using the round function? Seems straight forward but cannot see what to do....more >>

EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE GO When I execute the above statements on my local server, the 'Ole Automation Procedures' option is available. It is listed between 'network packet size (B)' and 'ope...more >>

Hello... Using MS SQL 2000, I noticed that an Insert Trigger gets fired when inserting an empty set of rows, i.e. issuing an INSERT INTO [target_table] ([column_list]) SELECT [column_list] FROM [source_table] where the SELECT clause returns no rows. Is that an standard behavior? ...more >>

Hi, I want to get a list of table names in one server/database. Say I want to get a list of table names in WorkDB database on ABC server. Could you show me the sample code? Thanks a lot! Michael ...more >>

Hello, the query below returns the data I am interested APART from there there is no value for JobItems.StaffID where NULL is returned of course. What I would like to happen is when there is no value for JobItems.StaffID to get the value from a different table, JobEngineer.EngineerID. I ha...more >>

Hello all, I have a need to display data as shown below. Z01 - Test, Z23 - ESFAS, Z50 - Hello World. The data structure is activity id project 1 Z01 - Test 2 Z23 - ESFAS 3 Z50 - Hello World The catch is that I can't use a stored procedure. Also there is no limit to the number of p...more >>

I have a problem: I need a function, which look into two tables. the first table must have da date. the second table has many fields and many records. now i must read the newesd, oldest, dateand compare with the date in the first table.if the date is not the same, then write the oldest date int...more >>

hi, i want to retreive number of characters before backslash(\). Example Reference_no 23424/456456 the number of characters before backslash is 5. please help me to solve this. thank you vanitha...more >>

-- Using MS SQL Server 2000 -- Ok... I give up... what's the proper syntax here? -- I want MyField to be unique in every row... EXCEPT if the field is '' (then duplicates are ok) DROP TABLE #MyTmpTable CREATE TABLE #MyTmpTable ( MyField VarChar(9) NOT NULL DEFAULT '', CONSTRAINT U...more >>

Running on Sql Server 2000 I have the following statement that was generated from my vb.net code. I got the actual line sent from my profiler and put it in Sql Query Analyser and got the following message: Server: Msg 201, Level 16, State 4, Procedure AddUser, Line 0 Procedure 'AddUser' exp...more >>

Hi all, I was planning on using the above in my stored procedure to get the ID of the row just inserted. My data type for the ID field is uniqueidentifier and when I try to save the query it errors telling me that my @MessageID which is declared as a uniqueidentifier is not compatible with...more >>