Hi, All: Please help. I use sql server as back end and Access 2003 as front end (everything is DAO). A table on SQL server has an identity column as the key. We have trouble on adding records to this table using the following SQL. strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E)...more >>

/* Hi I need to query this table to get results where ids are found with every searchNum, i.e. the results of this would be: id -- 1 2 because both id 1 and 2 are found with searchNum 1,2,3. The table could be any size with any variation of ids and searchNum so I need some s...more >>

i have a user who has delete rights on a table, but when i call truncate table statement it says not enough permission. how to assign truncate rights ...more >>

I'm trying to create a User Defined Function which will return only records with a date earlier than the current date. I tried using ,GETDATE() in the criteria block of the date field but when I try to save the function I get the error "ADO Error: Invalid use of 'getdate' within a function. I ...more >>

I have a fairly straightforward SELECT query that includes the following: MembersTable.MemberID IN ( SELECT ZipcodesTable.MemberID FROM ZipcodesTable.Zipcode IN ( '01234','03631','55902' ... '03036' ) That is, it's looking for entries in the ZipcodeTable where the Zipcode value is an...more >>

hello everyone, the documentation doesnt specify so i'm just gonna guess, but how would i specify multiple columns using the if update syntax IF UPDATE (Column) is documented i'm gonna try IF UPDATE (Column1, Column2) hope it works ty ...more >>

Hi, We have asp application (ado connection) which connects to the sql server 2000 (sp3). There is one asp page which sequentially executes set of 6 stored procedures. When I execute same set of stored procedures in query analyzer, I get response in less than 1 second.When application does th...more >>

I need help. We keep having deadlocking. The deadlocking trace points me to a statistic update. The KEY: 5:242972092:25 index lock it points to is a SQL Server automatically created statistic. It is on a foreign key column. I have tried turning autoUpdate Stats off and we still get the dea...more >>

SQL SERVER 2000 I have to tables Students: StudentSysID (INT) PK StudentName (VCHAR 100) NumTeachers (INT) Classes: SysID (INT) PK StudentSysID (INT) TeacherID (INT) I am looking for a query to do the following UPDATE Students SET NumTeachers = (SELECT DISTINCT COUNT(Teacher...more >>

Hi, This is my basic sql shape query: ------------------------------------------------------------ SHAPE {select * from tbl1} APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO field) ------------------------------------------------------------ With this query i get a Recor...more >>

Is it possible to create a view in TSQL that references a table that = cannot be verified. Is there some sort of override. I need to create a view that refences a table in another db. The other = db cannot be available during creation. Thanks, Robert...more >>

I have the time of an event stored on each record as a datetime field.It includes the year,month,day, etc. Suppose my user wants to search the table for all events over the lunch hour, say between11am and 1pm. How do I construct the SELECT query to peek into each datetime field and return only...more >>

I have a customer who wants to SELECT records based on a partial match in a text field. For example, in a list of telephone numbers they want to search for all records that contain the digits '777' in any part of the string. How do I formulate such a query? Many thanks. ...more >>

Hi, This is for the SQL 2005 (Yukon) Server. Can anybody tell me what the permissions "References" means on the Stored Procedure. Even if it is not possible to assign the "References" permission explicity on the Stored Procedure it is implied if we assign the "References" permis...more >>

Although I know there is a better way to achieve the same results, I'm just using the below queries based on Northwind so I didn't have to post a DDL. Problem: If you run the code in SUBQUERY PROBLEM below on Northwind, it returns the total freight for each day and the order subtotal that is ...more >>

I have a PHP web form that takes data and posts it to the SQL database. Instead of a record number being assigned to the first column of each row, the database displays <NULL> in the column. How can I get the record number to display in the database? When I run my second PHP script, it retr...more >>

Hello, I've got suprised that I had to change the code for a sample of my code, upgrading from 2k to 2k5... I have something similar with this code, which give me also different results (of course, supposing that I have 2 identical Dbs, one in 2k and another identical in 2k5): declare @Su...more >>

Hi I'm sure there is simple answer to thsi trivial problem, but I am completely stuck. I have got a table which contains 1000's of transactions of Claims, however I need to run a query of all closed claims. Below is a sample for one claim. Status Key is: 1=Opened, 2=Re-Opened, 4=Settle...more >>

When I do the following query: select convert(decimal(8,2),(1/3)) -- > returns .00 When I do the following query: select convert(decimal(8,2),convert(float,1)/convert(float,3)) -- > returns ..33 Is there a better way to do the division other than converting each number to float before ...more >>

Is it possible for me to return a very small number (5 or more decimal places) as a string NOT in scientific notation using the CAST or CONVERT function? I want 0.0000769 not 7.96e-005. Thanks, Martha Hoffmann ...more >>

I have records with a start date. I would like to add a column that is the end date, or the next record's start date. So if I had Store, startdate, retail 1, 10/15/2005, 2.50 1, 10/30/2005, 3.00 1, 11/19/2006, 2.00 The new records would be: Store, startdate, retail, end date 1, 10/1...more >>

I need to be notified of each sp that is beeing added or altered. I would like to create a trigger on sysobjects for this pupose. When I run the CREATE TRIGGE the following error msg received: CREATE TRIGGER permission denied on object 'sysobjects', database 'ABC', owner 'dbo'. ...more >>

It's a very common question: Input Item Detail ==== ====== A red B green B blue C red C blue C green D violet D blue D green D red E blue E violet F red For every item, i want one one detail (i don't care which one): Desired output...more >>

declare @today datetime set @today = '2006-01-12' select count( var1 ) from customer where var_DT > @today 1. Print or capture the counts for var 1 given 'where var_DT > @today' 2. Decrement @today (@today - 1) iteration 1. '2006-01-12' iteration 2 2006-01-13' ...more >>

I am having a problem install the software, it tells me the install is already running, or it gets hung on checking IIS. Any help available. Thanks Dee...more >>

I need to do some db cleaning, how can I get a list of tables within the database that do not have any triggers. Thanks in advance...more >>

Dear fellows, I would like to know how do I figure out which collation is established in a DB created in a Sql Server 7.0. I need to migrate to 2000 but I don't know how to see this information. Any input would be much appreciated. Regards,...more >>

Hi, Dont really know if this is the place to ask but here goes.... I have written a VB application that talks to various SQL2000 Tables. But i need to expand the VB App to work on a laptop thats remotely connected by VPN. Even though the laptop is connected via VPN and can use server appl...more >>

OK, I've now read a lot about the pivot table funcationality within SQL, but I need some help: (btw I'm running on SQL 2000, which according to my knowledge does not have the PIVOT function) I need to pivot information as follows: OrderDate StockCode BackOrderQty 01/01/06 ...more >>

Can I do something like this: CASE WHEN @orderBy = 'ASC' THEN ASC ELSE DESC END So I can order by asc or desc depending on a stored procedure parameter? ...more >>

I have a table that has cost records for a specific item for a specific start date. Start, cost, item, PromoCode, end date 01/02/2006, 2.45, 1234, R, 01/05/2006, 2.00, 1234, P, 01/08/2006 01/10/2006, 2.55, 1234, R, If a record has a start date then that new cost begains. And if it doesn'...more >>

DECLARE @Name varchar(50) DECLARE @Disk varchar(255) SET @Name='Databasename' SET @Disk = 'Drive:\path\' + @Name +'.bak' BACKUP DATABASE @Name to DISK = @Disk BACKUP LOG @Name WITH NO_LOG DBCC SHRINKDATABASE (@Name, TRUNCATEONLY) I plan to run this sort of logic on a week...more >>

I just can't seem to get the apostraphes right in this piece of dynamic sql. Any ideas? declare @marc varchar(4), @marc1 varchar(8) select @marc = 'marc' select @marc1 = 'QUINN_WH' EXEC('INSERT INTO marc_QMR_SNAPS_bug_Fix VALUES(' + @marc + 'Table Index created, getdate()') Server: Msg...more >>

How do I use ADO in VC++ 7.0 to get the size of the database. I know that sp_helpdb <database> returns size information. But how do I retrieve this information in my code? In my code I'm using _ConnectionPtr to execute commands, e.g. m_pConnection->Execute("EXEC procDoSomething", NULL, adExec...more >>

Dear all, I've got a little issue and I can't work out with it. Using BCP in order to export the contains of a .dat file into a table: C:\OFI0501>BCP abs..ABS_OF501 IN 20050726.DAT -e enric.txt -n -Sserver -U us1 -Pdts1 SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Se...more >>

Hi folks, I need to find out Drive capacity and free space, I do not have access to Windows. I just have sa access to the SQL Server. I used xp_fixeddrives to find out the free space, but how do I know the capacity of the drive. Thanks in advance. ----- *** Sent via Developersdex http:/...more >>

I'm thinking there MUST be an easier way to programmatically create a new database and login than the following steps we are doing below. If so, I would appreciate any info you could provide. Thanks, Tony 1) connect to master using sa credentials 2) Issue a "Create database X" command. ...more >>

Dear fellows, We are accostumed to work a lot of time with ETL processes such as DTS. We have got nearly 1,200 among three servers. Question is easy: Sql2005k provides us a powerful tool inlayed the own application be able to execute proficiently any kind of search? For example: statistics ...more >>

Hi all How to findout which column(s) in the tables requires index. Any method to follow?? Thanks in avance Guru...more >>

Hi, I have a query that works perfectly in Mysql but fails in MSSQL. Can anyone please throw light on this issue and guide me on how to avoid this? [QUOTE] select cust_name,cust_email from customer,cust_departments where customer.cust_id=cust_departments.cust_id and cust_departments.dep...more >>