How do I set up a DSN in Windows...more >>

Hi, I have table like this CODE Name DateFrom 1 d american 2004/03/06 15:41:28 1 c american 2004/03/06 15:39:23 1 b american 2004/03/06 15:39:23 2 burt walker 2004/03/01 12:46:20...more >>

I have a list of daily stock prices extending over a period of a year. I want to return the average for each week. IOW, I want 52 rows showing the average price for each 5 day trading week for the entire 1 year period. How do I write the GROUP BY to get the average for a week? ...more >>

Hi All, Sorry for the earlier incomplete post. Is there any setting in the server wherein I can disable the execution of multiple queries in a single line. For example: SELECT Name FROM Employee WHERE ID =5;SELECT Name FROM Employee WHERE ID=6; Returns two result sets. C...more >>

Hi, I am trying to return a unique ID number from a sp. I have a table (called IDNumber) that contains 1 row and 1 field, called 'lastNumber' Can anyone tell me if the following will work ok? I have about 400 users and I need to guarantee each call will return a unique number. I don'...more >>

I want to simulate a slow connection at localhost. Does SQL server has a function to sleep for a period of time? I check the book and believe I need to use a while loop with datepart(getdate()) to do so. -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-s...more >>

Is it possible to get a row number in a select transact query, something that shows the incremental row number plus the table data like this. 1 Jonh Smith 2 Mark Lopez 3 George Jones I need to get the column (1,2,3, ...n) Thanks. ...more >>

Can someone give me some advice on how can I go about in configure SQL = to be email enabled? I've setup MS Outlooks and tried setting up mail profile in SQL but when = tested it out at the operator, error 22022: SQLServerAgent Error. There's no Exchange or mail server, I can only use POP3 email...more >>

I'm having some trouble getting the right output from a stored procedure (simple version below). Can someone please tell me why I don't get a value in the @Return parameter? CREATE PROCEDURE TableUpdate @ID int, @Name @Varchar(30) @Return @INT OUTPUT AS UPDATE TableName SET Name = @Na...more >>

query: select field1, field2, case when () then 0.0 else 1.1 end AS 'Actual $', case when () then 0.0 else field2-[Actual $] AS 'Final $' from table the Query analyzer does not recognize [Actual $] in the second computed column ...more >>

Hi, I have an application who require to let users to upload files on server, and "link" them to some other database records (projects, which allow user to attach files to them) How is the best strategy to store them on web server? Should I store them in SQL server database, or as files o...more >>

Why did SQL Server complain about "invalid column recno" in the following query? It worked in another database tool I am using. select xx.pk, xx.amount, count(*) as recno from tx xx, tx yy where xx.pk>=yy.pk group by xx.pk, xx.amount having recno = 1 -- .~. Might, Courage, Vision...more >>

Hi I would like to do serveral updates in the procedure, so I passed in the ids as comma delimited string, but the ids are numeric field, I know if I use dynamic sql, I can directly use it, but how about just a update statement? Thanks...more >>

Is it possible to set up an alert via e-mail such that if any of the developers modifies a stored procedure in a particular database, an e-mail will be sent to me (describing which stored proc got modified)? ...more >>

I am trying to delete data in a table in a linked database. I tried to use openquery but this gives me an error without 'any information' - i assume because a DELETE does not return a result set. Using DELETE from [lnkdb]...[tab] ... is giving me grief because 'provider could not support a row ...more >>

Sorry about this, I'm a bit rushed and I don't have time right now to put together any DDL for it, but hope that the description will suffice ("Foul" I hear you cry!) I would like to use a UDF in a default constraint on a column, but the UDF will reference another table based on the value of a...more >>

Is there a T-SQL command that could lock a record in a table explicitely and unlock it within a stored procedures? -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.22-xfs ^ ^ 4:22pm up 2 days 32 min load average: 1.00 1.0...more >>

I have a join I need to use a "trim" function to strip the left 2 char off a field - can you help? here is the join left join safeway..xsfwy_Policies sp on sp.policy = trim("AG",aip.policynumber) Thanks in advance for your help ...more >>

f.e., I need to have select ...., calc_value when (condition) then (result of SUM) from table is it possible to do result of SUM when ever row I'm choosing from the table is a result of a JOIN. ...more >>

Hi I tried to use Charindex function toward a TEXT field to get a keyword ocurrance, if the keywords position is greater than 8k, it returns 0, I tried PatIndex, it works fine Is this because of the 8K problem? or is there a setting for this Thank Bill...more >>

Hi there, I created an odbc connection on machine1 that connects to mssql2k on machine2. For some reason I get very slow results between the two. Is there any way I can speed odbc connection? I'm using windows on both machines and created a system DSN using the sql connecter. Thanks! ...more >>

SQL 2000 I have a table that has ~5,000 records in it that I need to create some reports on. Each records belongs to one of 12 categories, and has a batch_date. I need a count of records for each category by month (for a given range) i.e., JUL | AUG | SEP | OCT | CMD 88...more >>

Hi All. I have two databases. One is called FTO, which is the main input database, and FTOArchive which, after every 3 months I need to "remove" records 3 months and prior to the ARCHIVE Database. Each database has 3 tables, tbl_OrderInfo, tbl_OrderItems, tbl_OrderLog which I need to get th...more >>

Connecting to a Sybase 11.9 system via W2K to export some data to pipe delimited for use in another location. Sybase bcp only allows me to export an entire table. isql -s\| doesn't seem to delimit fields in the right places. Don't have a problem with ODBC with Excel works fine but I run into som...more >>

I'm hoping someone here can tell me what is wrong with this INSERT Trigger I'm trying to create. The error I get is: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'UPDATE tblPhoneLog INNER JOIN TblPhone ON tblPhoneLog.PhoneNumber = TblPhone.phNumber ...more >>

Is it possible to have a many-to-many relationship without an associative or "linking" table? Also, is it possible to implement a many-to-many relationship with a self-join?...more >>

I have table with holidays date. I need to calculating the number of calendar days between two dates minus weekends and holidays (SQL 2000). How can I calculate in vbScript or SELECT statement? Could you please help me to resolve this problem??? ...more >>

For our current application we have to link dBase IV tables to our SQL 2000 server. I have created a linked server pointing to the dBase tables using 'Microsoft Jet 4.0 OLE DB Provider'. I can see the dBase tables in EM and I 'm able to work with them using the Query Analyzer. I can SELECT, INS...more >>

I have 2 tables with ID,type and name fields. I would like to create insert trigger on table T1, to insert the values in table T2 only when ID=2 and type=0. Something like this: CREATE TRIGGER testTrigger ON [dbo].[T1] FOR INSERT AS IF INSERTED(type)<>0 AND inserted(ID)<>2 RETURN else ...more >>

Hi, For security reason, I changed the "BUILTIN\Administrators" Server Access from "Permit" to "Deny". Run this one: EXEC sp_denylogin 'BUILTIN\Administrators'. But this will cause the SQLSERVERAGENT was failed to start . got the following error. I already changed the service login account t...more >>

Hello, most times the first connection after restoring a database results in the following Error: "Sytem.Data.SqlClient.SqlException: General network error." Even the workaround from http://support.microsoft.com/default.aspx?scid=kb;en-us;827452 didn´t help. Any ideas? Thanks Boris S...more >>

How can I build a resultset by using multiple selects? For each succeeding select I want to link to the prior select....more >>

I'm getting this error :Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. and I'm currently using SQL 2000. sqlcode: begin truncate testtable ...more >>

Greetings ALL I have a table wih a single column - x - I need to alter the column to make it an Identity column and I seem to have run into a brick wall What is the syntax for this - I tried Alter table tblName alter column X identity(1,1) and it doe not work. Do I need to drop and recreate t...more >>

Hi, I am trying to write a select to breakdown an ip address. for example: the ip is 132.213.212.47 the result should be: 1st 2nd 3rd 4th 132 213 212 47 the select needs to be able to change when ip address changes. new ip is now 133.11.21....more >>

Does anyone have an example of code for multirow operations with = triggers? I don't want to use a cursor. I know the trigger I wrote will work for = single row insert or update operations, but I don't want it to break in = the off chance a multirow update occurs. I want to write the best ...more >>

Hello everyone I am trying to look at trends with some raw data that I have. My data has a two values, a volume and timestamp dOctets as in First as datetim I want to find a way that I can run one query and group the data into "time buckets" so that I can graph. For example I want to SUM(d...more >>

what's wrong with doing this, I get an error on compilation for case portion of code @S_Selected integer .... Select ... from ... where ... CASE When @S_Selected <> -1 then AND tblA.Number = @S_Selected END AND .... ...more >>

I have an ISA server logging to the following table: CREATE TABLE [dbo].[Results] ( [ClientIP] [varchar] (32) COLLATE Latin1_General_CI_AS NULL , [ClientUserName] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL , [ClientAgent] [varchar] (128) COLLATE Latin1_General_CI_AS NULL , [ClientA...more >>

When generating SQL script for a UDF in SqlServer 2000 the script includes these lines at the top: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO < drop function statement> <create function> .... When a function is created with ANSI_NULLS OFF it can not be used in an index (on...more >>

In a SELECT statement, I want to remove all non-numeric characters from a column. (i.e. only return characters "0-9"). I know I can do this in the client and then store the resulting value in a column, but is there a way to acheive this without doing a REPLACE() 245 times? Source: 388 So...more >>

Hello, I am working with a table with about 27 million records. When I run: SELECT count(*) MyTable It takes several MINUTES for it to return. The disk activity goes up to 100%. Is this normal behaviour? If so, why? I would think that SQL Server would be able to easily calculcate that ...more >>

Need some help to have the results of the following code come out as either a .tab delimited, with " " text separators; or .csv delimited with " " separators. What code can I add to make this happen automatically CREATE PROC dbo.xbke_my_car_page_us @begin_dt datetime @end_dt datetim A ...more >>

I've read some articles that J# will be supported in Yukon and others that say not. Does anyone know? Where can I find these answers?...more >>

Hi, I have a table with 4 columns where I must check if there are duplicates, it is a duplicate if all 4 columns match another record. How should I construct the query to achieve that? Thanks Lasse Using MS SQL 2000 ...more >>

Hi, I am working on an extract from our SQL Database. I need to obtain various sales values and put them into a text file. The problem is when I try to convert very large figures. When I try and convert from float to varchar the figure is always converted into exponential notation. Th...more >>

SQL2000 sp3 Advise please i am trying to write a query with a having clause, basically i want to find out what weekday it is, if its a monday then give me todays date - 3 days, otherwise give me todays date - 1 day in access the clause looks like this "Updateddate <(IIf(Weekday(Date(),...more >>

When designing a database, is it better to have one lookup and types table or many. For example, in a real estate database you could have one phone_type table (mobile, home, work,...) for clients and one property_type table (appartment, house, land, cabin....) or contain all these values in one tabl...more >>

I need help with a query. A subset of the data table is listed below ParentID ChildID FriendlyNum SubID DEL_IN 3 3 1000393 -998999607 3 3 1000395 -998999605 3 4 1000395 -998999205 3 3 1000776 -998999224 3 4 1000776 -998999204 I need a query that will do thes...more >>

I have a SQL stored procedure that is returning results in a table as follows (Below): I would like to have the results grouped by price so as it only appears the once. I would like to have it so as the copies and Rev totals for each price are summed up. To show exactly what I mean please look a...more >>

Hi, I have the following query that kinda does what i wan SELECT ABTANumber, TourOperator, ReportStatus, COUNT(*) AS Counter FROM (SELECT ABTANumber, TourOperator, r.ReportStatus FROM bookingdetails bd LEFT JOIN report r ON bd.Id = r.BookingDetailsId) a GROUP BY ABTANumber, TourOperator, Report...more >>

How do I write a store procedure that limit the amount of record a member can upload. Example: I have a student database that allows students to post-up there school books, but they can only have five books posted at one time. So how can I create a stored procedure that will do the above? ...more >>

Hi, I have to import a file, using BULK INSERT, into a #TEMP_TABLE - the file has the following contents/format (5 cols): "B09Z003 ","0058-003-01 ", 57, 0," " "B09Z005 ","0053-003-R1 ", 19, 0," " "B09Z008 ","0054-2 "...more >>

Hi All I believe that you cannot nest a cursor and use the @@cursor_status because the @@Cursor_Status is a global variable. My problem is that I have several functions and sp's use Cursors and one sp will call another with the cursor. and this seams to work fi...more >>

Hi all Please help me on this one Trying to render a swedish kalender in SQL-server is no problem, but trying to get WeekNumbers connected to the date is a problem, at least, if you trying to get the week number for 2003-12-29 (29/12/2003) SQL-server will return week 53. In many countries this mig...more >>