I'm trying to keep a running count of the total number of files that fall within a particular date range. For example, if you have a file that was created last month, 'Within 1 month' = 1. However, 'Within 1 week' should also equal 1. Right now, this is what I have so far: SELECT CASE WHEN ...more >>

would appreciate any recommendation for good books on sql 2000 tia, dk...more >>

I have this stored procedure that takes a few parameters like date and merchant ID, and basically goes through a set of if-then statements to build a SQL SELECT string. When we upgraded from SQL Server 7.0 to 2000, the stored procedure still worked from Query Analyzer, but not in BCP. It used...more >>

Hi, I'm having some trouble with the proper syntax of a CASE statement. I'm using sp_executesql in a dynamic search sp that I wrote. Here's the portion I'm having trouble with: SELECT @sql = 'SELECT CASE WHEN NumberOfDays <= 7 THEN 'Within 1 Week' CASE WHEN NumberOfDays <= 30 THEN...more >>

Can anyone see anything obviously wrong with this statement? I'm not really an SQL kind of guy SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND CategoryID = 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title PlatformID contains a GUID and I thought passing a wildcard woul...more >>

Hi, I have the following data in a table: ClientID LastName FirstName Address City PhoneNumber 1 Smith Thomas 111 Seven Road San Francisco 555-2415 2 Dennis Tim 222 Seven Road Chic...more >>

Hi, I have a table like the following and I want to see the update field to have the latest time when someone updated that particular row. How do you create a trigger to do this. only one table involved here. Thanks Create table User ( userid int 8 not null, username...more >>

I'd like to know what's the sentence to display just duplicated fields. Example: I need to know where are duplicated mails in my database. Thanks. -- Marcelo Balgurevich info@designmarcelo.com www.designmarcelo.com ...more >>

Hi, I have two tables (PRODUCT and CRITERIA) , linked by an N to N relationship. I need to select all the products that match a set of criterias. For example, I need to find all the products that are at least 'red' AND 'in bottle' I've setup a table (FILTER) containing the criteri...more >>

Hi all, I've a table with data like this: ADDR TYPE Num 3016 9 1 3016 10 3 3016 5 2 5054 7 3 5054 10 2 I would like the data broken out like this, without using a cursor: ADDR TYPE Num 3016 9 1 3016 10 2 3016 10 ...more >>

How can I use a button in an MS Access Form to use a field on the form to run a report based on a stored procedure? If all was in Access I can do it but I cannot get the sp to filter the records. Form has ID field and a button Report has sp with data relating to single ID Thanks in advan...more >>

I've got some maintenance stored procedures I wanted to group into a single stored procedure and run. Everything runs great individually. However, whenever I try put all the stored procedures into a single stored procedure I'm finding some of them aren't inserting all the records. When I pul...more >>

Is it possible to use For Loop, Do while Loop or any other kind of loop inside the select statement. For example: Select partno, desc, comments from table 1. Comments is a memo field therefore I cannot copy the content of it to excel therefore if I can use some kind of loop statement and ...more >>

I'll make my post clearer now. I have an application, the user are able to change records in this application. Also, there's a button called "What If". After the user pressed the "What If" button, the user may change the records and save it. When the user open the same record again, all the...more >>

I have a data logging app that maintains a table... MachineID int, ReadingTime datetime, MeterReading int MachineID ReadingTime MeterReading 1 01-Jan-2004 09:45 AM 0123 1 01-Jan-2004 10:00 AM 1234 1 01-Jan-2004 10:15 AM 2345 2 ...more >>

I want to select fields with datetime data type. EffectiveDt in table tracking has datetime data type. However, when I do the following, it doesn't return any rows even the data exists. Any ideas SELECT FROM trackin EffectiveDt = '2/20/2004 Or I tried not to have quote in between t...more >>

Hi, Can someone let me know how to break a list to several smaller list? The following won't work and have duplicated entries. Size is wrong too. Thanks select top 25 percent f1 from table select top 50 percent f1 from table where f1 not in (select top 25 percent f1 from table) select disti...more >>

I'm going to perform a detach, copy the data/log files to another server and then run the attach command. How do I go about syncing the logins? Is there a proc I should run? Please advise Thanks Susan ...more >>

I'm trying to join those three queries on the wsp_00.AM column (which is the group by column). The only way I was able to do it so far is to save them all as views and then join those the views like you do regular tables. The questuion is wether it's possible to do in one batch? Here is the sym...more >>

I'm new to SQL/VB.Net. As part of a learning exercise (and as part of a windows application I'm playing with) I'm trying to load an MSDE SQL Table from an Excel spreadsheet. All parts of the code work prior to the Insert command execution. Can someone please point me in the right direction? ...more >>

Hey I recieve a error from my backup job, it says the backup was envoked by another user, but when I look the backup is there, and I can restore it an data are correct I have tryied to delete the plan, and make a new, this usual works, but not here What can it bee?? Any idea?? Please he...more >>

I need to do this join: table1 left join table2 on Col1=Col2 --read Col2 until find "-" field data example: Col1 11111 222222 33333 Col2 11111-xyz 222222-xzz 33333-yyx Thank you in advance for your help. Olivia ...more >>

Suppose I have a store procedure : Select * from table1 where qoh > 0 How do I call it in VB6 ? Thx. ...more >>

I'm trying to do something like this, I'm getting a syntax error near the keyword BETWEEN, can someone corect me.. SELECT COMPANYNO, RFC, WARSEQ, CASE ENTRYDATE WHEN BETWEEN '2000-06-03 00:00:00.000' AND '2000-06-03 23:59:00.000' THEN 1 WHEN BETWEEN '2000-09-30 00:00:00.000' AND '2000-09...more >>

Hi all, I need to allow my users to change their passwords from a remote Visual Studio Windows application. What kind of SQLServer calls could I use to do this? I found sp_password in the manual, but that seems to do password changing in the clear. For security purposes I need to send th...more >>

Hi, I have a long string which I'm passing in through VB. it's declared as a adLongVar in VB and a text in SQL. There is some sort of compatibility issues. Any ideas what I should look at? thanks ...more >>

I am having performance issues with the following stored procedure. It will take around 10 seconds to return 4000 rows. Most of the parameters are optional. When I remove the CASE statements in the WHERE clause and just use the parameters @CompID and @WOStatus1 through @WOStatus4, this ...more >>

I have a stored procedure that returns a different number of rows each time I run it, and it shouldn't be. When I run the code that's in the sp, it works fine. Same number of rows every time. It seems that when the stored procedure is run, some rows in the resultset are dropped somehow. I've nev...more >>

Hi all, Is there any datetime functions in MS SQL Server 2000 that works with different day count basis like 30/360 or 30E/360? ( like DAYS360 function in MS Excel ). Thanks Dimiter ...more >>

sp_helpconstraint <con_name> does not display the text of the constraint. Has anybody idea how can we get this. TIA....more >>

I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and BattID are a TEXT datatype and ChrHrs are a number(long...more >>

Hi, I have a stored procedure with about 20 parameters. Do I have to declare 20 variables to call this sp? Is there a method allowing to call procedure with parameters from fields returned by select statement? I'm looking for someting like this: " exec my_proc @my_param = my_field, @my_param2 ...more >>

I'm building a SQL String and I want to choose only the records that are part of the current contractID. I have stored the contractID in a variable and put it in as follows WHERE (JS_CRM.dbo.cntrct_cntct.ContractId = '" & contrID & "') The variable is stored as type String and the contractI...more >>

I need help with the following 1) Directory A (source)has many files named: ABC020120041 ABC022020041 ABC022020042 Where the ABC will be static, then the date then a counter. 2) I have the code to manipulate the data. 3) I need to output as: ZYX022020041 Where the ZYX is static, then ...more >>

Is_memeber is not always returning the correct result When I run the function using my credentials like this Is_member(N'db_owner') it returns 1 - fin when I add myself to a role called 'Developer' and run thi9s again using is_member(N'Developer') it returns 0. Note, I am NOT 'dbo' but I am...more >>

Hi, I made an insert using a Stored Procedure (MS SQL 2000). How can I store the ID of the last inserted record into a session var ? I have code to do this with a regular "Insert" page (Using Dreamweaver), but didnt work for the SP. This is part of the code I have for the insert for the ASP...more >>

Hello Im running sql 2k and i need to attach a 7.0 db. is it possible to just attach it or do i have to do something else? /Lasse ...more >>

Hello all. Do you know how I could tweak this code to use an sp return value within another sp? I have 2 sp's sp1 ans sp2. Sp1 is compiled it receives a username varchar(250) variable and returns an int value. Sp2 should be able to exececute Sp1 and use its return value. My dysfunctio...more >>

The following is the code used. Jim Johnson prosed a rather long method. I would suppose that all I need is to read 254 bytes at a time, but I am also reading the rest of the table (one whole record). '**** this is the connection to SQK server cn13.Open DSN=SECWAT;UID=sa;PWD=sa;DATABASE=...more >>

I've tried posting a handful of messages with full DDL and DRI and stuff but they've tended to be long and folks have avoided them :( Hopefully I do a better job this time. Does anyone have suggestions on how to model a data entry form? I need to model something that has nearly 100 questions ...more >>

Hello i have description in column of my table for example: Table: Tbl1 Columns: col1 (description: Important column) col2 Where SQL Server save this information of column description (Tbl1.col1.description) ? Can i access to this information ? Thank you ...more >>

Can anyone help point me in the right direction? I can't figure out why my stored proc isn't returning the correct results when I call it from an ASP page. Thanks in advance!!!!!!! SP: CREATE PROCEDURE spSearchProducts @SearchField varchar (50) AS declare @SearchFld varchar (50) se...more >>

Any help is appreciated I have a stored procedure calling by ADO.NET. The SP hangs up each day (Sometimes OK and sometimes Hang up) and the only way to make it run again after hangs up is to recompile it. I don't know why. I guess it may cause by the subquery "NOT IN". The query is self-locked even...more >>

...more >>

Summary of the problem: We need a mechanism that allows our client application to send a custom-generated identification string or ID-number to SQL Server via ADO Connect object. This ID number should be visible in SQL Server trigger procedures. We are trying to accomplish the following: - ...more >>

Hi, I have an application, in which there's a button called "What If". When the user pressed this button, every changes they made on the records displayed in form wouldn't affect the real records until the user press another button called "Accept changes". Is there any way i can do it via MS ...more >>

Hi all, I have two tables, table1 with datetime (smalldatetime) and value (real) fields and table 2 with datetime (smalldatetime) and OnOff (bit) fileds. Table1 (contains records for every minute) Datetime Value 2004-01-01 00:00:00 20.10 2004-01-01 00:01:0...more >>

I'm stumped, could someone point me in the right direction? I have an web app that updates a table in my SQL 2K DB when employees "clock in" and "clock out" from work every day. If they don't come to work, no record is created in the table that day. It works great, however, my boss now w...more >>

My first attempt at a SQL Server stored procedure is not going well. I am trying to update a record in table 'Personnel', via a unique key 'ID' from an entry in another table The second table, 'Job_History' is indexed by a combination of ( the unique key value from the first table, 'EmpID' ascendi...more >>

Hi all, I have the proc below output only the first record and I need six records to be output. I know the OUTPUT parameter return only one row at a time. I have 10 procs like the one below output to the final proc. Is there a work around to output 6 rows or is there another way to d...more >>

Is there a way to encrypt data in a table, or more specifically in a certain column? Thanks! ...more >>

I have a comma delimited file with one field that contains values such as 0x20090BE, 0x20090BF, 0x20090C0, etc. This field apparently should be an int(4), but I can only import it into a field which is varchar(20) for example. When I use the DTS Import wizard, is there a way to convert that t...more >>

Hi all What is the max number of characters allowed in one job and is there a way to expand this limitation Ray...more >>

Hello All I am trying to develop a specific allocation procedure like in Analysis Services some of the allocation types I want to do in a T-SQL stored proc are like the - Account allocation, Time allocation, Percent allocation, etc. Can any of you please point me where I can find some sampl...more >>

I want to delete records in tableA based on a join with tableB as follows: delete Tablea Where TableA.Col1 = TableB.Col1 and TableA.COl2 = TableB.Col2 And TableA.Col3 = TableB.Col3 And TableB.DeleteFlag = 'Y' How can I accomplish this kind of a delete can you please provide some tips. ...more >>

I have a backup file of a sql server 6.5 database but can't bring it into my sql server 2000 database. It appears I need to re-install 6.5 to do this but have long since lost the install file for 6.5. Can anybody advise me? Any help really appreciated Rob...more >>

Can anyone help me work out the following syntax error. The procedure works when I manually pass in the parameter values and run it in SQL Query Analyser The error message im getting is as follows: Error 1023: Invalid parameter 1 specified for datead CREATE PROCEDURE dbo.tester12 A INSERT ...more >>

Hi all, newbie here I want to treat my sp like a function in a select statement. ie Select memberID, dbo.fn(memberID) as NumberofDays, exec sp memberID as NumberOfWe...more >>

Hello, Could someone please let me know which provider does Query Analyser use? I am using SQL 2000. Thanks...more >>

Hi, I'm experimenting with SQL security, creating users and access privileges, and so on. I'm using the 'pubs' database for practice. I've set the 'public' role to REVOKE all permissions on the table 'authors', (At least I think it's revoke - the box is empty, no tick or cross appears) ...more >>

Dear All, Is there a way of turning off the auto exiting of store procedures when there is an error ? Thanks Peter...more >>

I have recently inherited a system from Access and am having some difficulties understanding what some of the following functions are doing as part of a Select statement. Can anyone tell me what the four parts are doing. I think the first to are converting the data entered into the satfri form to ...more >>

Hi there, I would like to see the relations between tables and stored procedures. Is there a tool out there which does this? Is there a script somewhere which acomplishes this? I was thinking of trying to write something myself, the task is staggering, I think. I was thinking of searching fo...more >>