based on BOL, bitwise AND operator operates only on integer data type category (int, smallint, tinyint). but i have a char(4) column which stores integers (disregard the db design issue) which produces the same/correct results after using bitwise AND with another integer. does this mean SQL ser...more >>

I have a need to update a table with data from another. They do not even share the same field names. although do share the same data types and meanings. I am just having a problem wrapping my head around this. I can't seem to shake the feeling that I am going to update the entire table by ac...more >>

Okay, SQL gurus. I have taken all of the examples into consideration & have been successful. This is what got: Job_Title_Code Salary Job_Title_Code Min_Salary 106 47500.0000 106 32500.0000 100 35000.5500 100 45000.0000 102 55000.0000 102 ...more >>

In my SQL class, my professor wants us to: Write a SQL query that joins two tables in the example databases and uses BETWEEN to restrict the record selection (Use salaries to restrict the data). Here are the tables: Employee Social_security_number Last_name First_name Address Ci...more >>

hello i want someone to help me in solving a problem in sql server 2000 considering that i have a table named PAY(TITLE, SAL) where TITLE is the primary key of this table also this table is related to another one named EMP, where the other table has a foreign key to this table. ta...more >>

I need to select a list of random records from a table. The table has a numeric ID column that is a primary key (identity property). I would like to return a set of 1000 records where the only criteria is a random ID number. I can come up with a cursor solution that does what I need but...more >>

I have a table (EmployeeTimesheets) that holds hours and dollars for each pay rate for each employee. I have another table (PayInfo) that holds the check information for one or more of the EmployeeTimesheets. I want to get the current data from the EmployeeTimesheets table as well as the YTD ...more >>

In the EM, using the TaskPad view, I see the TempDB usage data and it shows 4 numbers, database used and free, log space used and free. Which are the commands to see this exact information ? If I do : Use TempDB Go EXEC sp_spaceused It does not show all of these. ...more >>

Hi All, What's the best way to notify caller's about any updates on a given table? If a client is holding an ODBC Connection how do we notify them that a update has occurred? TIA ...more >>

Hi I am a novice at this so please bear with me. I want to create a trigger that will update a custom table STATUS whenever the STATUS is updated in the PROJECT table. I want it to write the value as it was, the updated value and the date of the update. Thanks for your assistance. Sa...more >>

There have been some pretty advanced stuff here I've gone through, but I am a ..NET programmer by trade, so T-SQL is a bit different for me. I have an outside list of items with ID codes that I need to import to an existing table, but I need to have the procedure check for dups in the excel ...more >>

Hi all, i have this problem i´m using the newest SQL 2005 and i need to create a pivot table, and my problem is my pivot table is something like this COL1 COL2, COL3 , .... COLN ROW1 1 3 0 ......... ROW2 4 2 4 . ......more >>

Is there a way within a proc to determine what object called it? ex: DB1.dbo.ProcA calls DB2.dbo.ProcB. From within ProcB, is there any means to get the context (name or id) of the procedure or function that called it? Thanks!...more >>

How do I find the latest record? Original data C_ID M_ID DATE SCORE ----------- ----------- ---------- ----- 2467 14843 2005-09-27 45 2467 55877 2005-09-26 89 7392 12365 2005-09-26 98 7392 199128 2005-09-1...more >>

We have a comprehensive windows based application which uses a database other than SQL Server. We have been considering moving our application to SQL server but have been stymied by limitations in the ANS SQL implementation as well has hidden restrictions in Decalarative Referential integrity...more >>

Does SQL Server 2005 still support the *= outer join syntax without having to set the DB Compatibility switch? A friend of mine tested out SQL 2005 Beta 2 and said that *= was still supported, but then when the official SQL 2005 release came out, *= no longer worked without having to set the ...more >>

Hi All, I have an SQL statement that needs to get two sets of data based on the same field. In our program, the system will roll over the number to 0 when it hits 9999. When a user wants to check production data, there are occasions where the starting number is greater than the ending num...more >>

Hi, I would like to get Row IDs to number my resultset from 1 to whatever. For example, if I have 10 records from the following statement : select FirstName, LastName from employees I would like to number the records from 1 to 10. How do I do it? No cursor please. TIA ...more >>

I keep getting this error and cannot figure out why. All jobs are running fine, and there is not replication going on. I seem to get this error about every few minutes...more >>

Hello everyone, I am new to SQL Stored Procedure, I try to get the 10 newest records from 3 tables, and created the following procedure: CREATE PROCEDURE [dbo].[sfDevLastUpdated] AS SELECT TOP 10 * From BOOKS, EDOC Order By BOOKS.DOP, EDOC.DOP Desc; GO But the server always returns only...more >>

I have a decryption routine in a SP. I want to return from it the "plain text". This will be called from a VB web based app. How do I get varchar returned instead of int? All process I have found so far are based on one SP calling another. TIA. ...more >>

Dear Experts, During the execution of a select statement i faced this error message. "I/O error 23(Data error (cyclic redundancy check).) detected during read at offset 0x00000190efa000 in file 'd:\sqldb\MSSQL\data\Data2005_Data.MDF'." Pls. advise solution Thanks in advance. S...more >>

I'd like to make a trigger such that every time the MbfUser table is updated, the users are first deleted, then (re)added to the MbfRole table. The two tables are normalized through the MbfRoleUser table. The Delete statement is failing. Here is what I have so far: CREATE TRIGGER UserUpda...more >>

Hi, I'm pretty new when it comes to programming sql-quaries. But I've heard that OUTER JOIN is much faster than NOT IN wich result its the same. Can anyone tell me how to rewrite this quary in a proper way to get the result using OUTER JOIN instead? The task is to display a list in a database...more >>

Helle, friends, We have tables that need ID fields to uniquely identify a record (primary key), such as userID, fileID, docID, deptID, etc. There are two ways to hand these IDs: (1) Create an AllIDs table to keep the ID name and its last value. Each time when we need to insert a new rec...more >>

Hi EveryOne : I need to set the execution of an exe file, every time my application inserts a new record into a table. im thinking to use xp_cmdshell into a trigger: Now this Table is which will be storage into and SQL MSDE evironment so These are my question . A) Does The xp_cmdshell w...more >>

We have a web server which needs instant results from our SQL Server. We also have a lot of reports that are run at various times of the day that hit against a few multi-million record tables and it bogs the SQL server down. This in turn slows the response of the web server. Can I setup two...more >>

Hi i have a table with different columns: columns names: jobA, jobB, jobE..... I want to create and index with all the values(but not duplicates) contained in all columns. Can anybody give an idea on how to do this? Thanks ...more >>

I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7, but don't have the experience to understand this enough. I also looked in previous questions on this group, but find I still need help. I need to insert a series of MSR (Medical Service Record) numbers into a table of ...more >>

Table SALES: gasstationID salesDate gallons productClass = GAS or DSL I need to group gallons by week by productclass for the last 14 weeks (last week) and put them in this format: gasStationID productClass week01 week02 week03 weekn... week14 1001 GAS ...more >>

I have been using SQL in SAS and now I want to continue to learn SQL outside of SAS. I have a couple of questions: 1. Can someone suggest a good workbook to learn SQL? 2. Where can I get a good editor for SQL code? I really like using SAS for SQL because I can submit only the statements I wa...more >>

Hi I have a table that has fromNode, toNode and idGroup columns I want to return the TOP 3 toNodes for each IDGroup that the FromNode = 1000 E.G. The query would return fromNode, toNode, idGroup 1000,2001,1 1000,2002,1 1000,2003,1 1000,2004,2 1000,2005,2 1000,2006,2 1000,2013,3 ...more >>

Hi, I have a cursor that reads a table. During the reading of the table I want to read e "field" directly (Not usign FETCH NEXT FROM authors_cursors INTO @col1, @col2). Thanks!...more >>

Can one do a 'Delete from Select' SQL Statement? SELECT distinct Vh_make_id FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (select distinct Vh_make_id from marc1) AND vh_make_id != '' -- 154 Rows I want to delete all rows from the vehicle table that have a matching Vh_make_id in the marc1...more >>

I am creating a stored procedure to send emails (with xp_sendmail, I think) based on certain conditions. This is my logic: I need to loop through all of the Table1 records. if Table2 exists for Table1 and Table2.column='T' send variation 1 of email else if table2 exists for table 1 and T...more >>

hello, if i run direct from the server double-click (ImportCustomer.exe) - all works ok. If i run from query analyser: USE master EXEC xp_cmdshell 'E:\scripts\consoleapp\ImportCustomer.exe' the returned error in the queryanalyzer is: NULL Unhandled Exception: System.ArgumentException:...more >>

Hi Is there some internal setting within SQL Server that can override your group by date ?? I have a query that I am trying to group by a Monday and I am using : DateADD("wk",datediff("wk",0,TA_DATE),0) as 'Week Starting' to do my select & group by This brings back Week Starting ...more >>

I test my C# applicaiton both with Access and SQL Server database. I note that the following code snippets takes 24-35 seconds with Access database while takes only 2-3 seconds with SQL Server to fetch same number of records (80,000 records). //with Access Database this.da = new OdbcDataA...more >>

Hi ... I am modifying a T-sql code block to eliminate multiple record sets, and each elimination set is based on different criteria. Given the existing code, the simplest way to do the job is to add a block of delete statements. Problem: I don't want the deleted records to be inserted in...more >>

hi, my query Select A.COL1,B.COL1 from A,B where convert(varchar(8),A.COL3,108)= convert(varchar(8),B.COL3,108) provided COL3 is part of the key in both the tables. So if we try to convert the index of the table to time alone the performance of the query is affected and it's taki...more >>

Hi, I have the following table: Year I J Amount 1990 124 220 13.45 1990 124 221 -3.45 .... 1990 148 190 123.4 .... 1994 124 220 4.44 1994 124 221 -3.45 ... etc. Th...more >>

Hello, I have a table with about 2 mil records, these are grouped. Each group contains about 1500 records. Now the purpose of this is to get a moving average of a certain stock. Than means that it gets the close price of the last 10 or 30 fridays and averages that. Now that's what the funct...more >>

My stored procedure - mst_sp_FetchReceipts is declared : CREATE PROCEDURE mst_sp_FetchReceipts @sourceid INT, @fromtime DATETIME = NULL, @totime DATETIME = NULL, @maxtransactions INT = NULL AS < Procedure body> The call: exec mst_sp_FetchReceipts 1, '2005/11/04 08:00:00', '2099-...more >>

Hi All Wondered if you could help me with the below query. I have 1 simple table called STOCKCATS that consists of 2 fields. These fields are called CATID and LEVEL. The contents of this table are as follows: CATID LEVEL cat01 <nothing> cat02 <nothing> cat03 ca...more >>

Hello! I would like to write a query that returns for instance column 2 if the name is not known. Is that possible? Eg: If I have a table with 4 columns, I should be able to write code that returns the third column: SELECT 2 FROM MyTable Of cause this just gives me 2, 2, 2 etc. How to ...more >>

Hello All, I found an issue and wanted osme advice or guidance I have found an issue in the ODBC API when getting the column type of a BIGINT. When the column type is defined as BIGINT in the table then all works fine. The column type is 127 (x7F) and size of 8 bytes But if the I use co...more >>