I have Windows XP professional installed on my computer, with SQL 2K personal edition. I try to use enterprise manager, return all rows in any table, and i get the error "provider cannot be found. It may not be properly installed" I tried installing mdac 2.7 sp2, mdac 2.6 sp2. tried registerin...more >>

Hello Sql Server Gurus... We currently have a db where the database has gotten rather large rather quickly. ( We are storing images in the db ). Our customers are beginning to approach tape backup limitations on their current configuration. (Around 120 GB). Our solution to this is to create A...more >>

Hello all, Can I use a if condition in the select query. For example "Select date from table" -- I would like to replace the date with spaces if it is like '1/1/1900' Any ideas how I can write this query. Sunita...more >>

Hello I have many different tables, that most have the same structure.. they all have some primary key, some fields and mylinkid ... so what I want to make is to create a stored procedure, where I can pass the table-name, primary key, and the new mylinkid, what should happen is, that the proce...more >>

We've had swings in restore times for logshipping (SQL2k) to standby(Reporting) server. For example last night, it took 3 hours to restore 2.8G of trn files. This should take 1 hour at the max (even less if you ask me). My question is - is there anything within the SQL Server engine that I can v...more >>

There have been a few astute posts here and there to the effect that notwithstanding the benefit of the development of "the relational model" for databases, for the last 20 years database theory (a la Date for example) has remained database centric in its thinking. Is this a fair summarisatio...more >>

Well... I've passed the Design and Implementation and the Administration certification exams, and, based upon a really good interview, and a broad base of other computer knowledge, I managed to land a job where I'm responsible for database administration, after a fashion. My boss is preparing...more >>

I'd like to add an extra column to a SELECT.. query to a table, The table looks like ID WorkDate Activity 1 2/20/2003 Xxxxxxx 3 4/10/2003 Aaaaaaa 4 3/23/2003 Bzzzzzzz 6 3/10/2003 Nnnnnnn ..... I want ...more >>

Forgive my lack of knowledge, but what is the maximum size that an int datatype can support? As the default, it appears that the size is set to 4 (which would support integers up to 9999, correct?) So, how high can I set that size before going to a bigint type? Thanks for any help ...more >>

Hello -- I'm currently working on a VB application that will essentially be a front-end monitoring application using SQL Server. The front-end application will simply read data from SQL Server and display in a datagrid. What I also need is a backend application that can run as a NT service and...more >>

got slightly similar table. #Temp Table: name,name1,name2,exam,score A,A1,A21, A,A1,A21,math100,88 A,A1,A21,math101,56 A,A1,A21, A,A1,A21,math102,67 A,A1,A21, A,A1,A21,math104,45 A,A1,A21,Chm100,55 A,A1,A21, A,A1,A21,chm101,86 A,A1,A21,chm102,44 A,A1,A21, (skipping) A,A1,A22,math100,...more >>

got slightly similar table. #Temp Table: name,name1,name2,exam,score A,A1,A21, A,A1,A21,math100,88 A,A1,A21,math101,56 A,A1,A21, A,A1,A21,math102,67 A,A1,A21, A,A1,A21,math104,45 A,A1,A21,Chm100,55 A,A1,A21, A,A1,A21,chm101,86 A,A1,A21,chm102,44 A,A1,A21, (skipping) A,A1,A22,math100,...more >>

I'm a rookie with MSSQL. I need to run a DTS package to export a result set to an MX Excel spread sheet. I need to call the DTS from a stored procedure and pass it three values, depending on the input parameters to the stored proc. DTS package is no problem. Pretty easy with the DTS wizard. My...more >>

tentaively, got: create #table(a,b,c,d) insert into #table select A,B,C,D from s,t,x,y,z where y.price > 20 select *, (select max(D) from (select distinct top 2 with ties from #temp v where v.B=W.B and v.C = W.c), (select min(D) from (select distinct top 2 with ties from #temp v where ...more >>

Dear all, I have a simple SQL statement below. insert into studentRelativeContact(userID, [order], exist) values(999, 3, 1) The above executes successfully in the developement platform A, but this statement fails to be executed in another platform B with error code 156 which indicates sy...more >>

table(name,age) need to find top 2 that are distinct by age. error check: if two names are the same go to next top age). table(name,age) A 17 A 17 <<error B 14 C 16 D 16 got a table like that and select distinct top 2 age from table, returns: A 17 A 17 -- ...more >>

#Table(class, name,exam,score) A name1 math 100 A name1 math 88 A name1 Phy 98 A name1 Chm 98 A name1 SPT 89 A name1 math 23 A name1 math 45 A name2 math 54 A name2 math 79 A name2 Phy 79 A name2 Chm 44 A name2 SPT 34 A name2 math 45 A name2 math 65 B...more >>

Over the past 6-7 years, I have seen companies sell custom software that costs thousands of dollars and are the most poorly written applications. For example, we (the company I used to work for) paid $60K from a company for specialized software targeted for data processing centers that transmit...more >>

Hi, I want to create a view on a table containing raw plc value data that shows average, min, max values every specified period. The first one I'm working on is hourly data... I've the code below to create the view: SELECT DATEPART(yy, ItemHistory.[Timestamp]) AS Year, DATEPART(mm, It...more >>

I do a lot of SQL just about every day. I generally use != and !> and !< instead of <> and <= and >=. Mostly because they equate to how I actually think about it. "Not equal to", "not greater than", "not less than". Some of my co-workers, after they stopped laughing and got up off the floo...more >>

Hi guys, I just installed MSDE2000 (SQLServer Desktop 2000) on XP Professional. All seems to run Ok. With MSDE and SQLserver 7 I used to be able to place a database from one of my customers in the data directory of sqlserver and the server would see that DB (seen e.g. by the enterprise mgr or...more >>

There is probably a real simple answer for this but... how do i pass a money value into a called stored procedure? Exec usp_updateEmployee _ @EmployeeID = 1250, @Salary=@$200,000.00 the commas are killing me. thank you so much for any help. ...more >>

Can someone point me in the right direction to figure out what's wrong here? I'm trying to copy a very simple DB between two servers using the SQL 2000 Enterprise Manager DB Copy Wizard. All steps looks good up to detaching the source DB, but then the actual copy of the share database mdf fil...more >>

Hi There, I have a SQL 2000 Server on which I've created a database, that in turns contains a number of tables. A proprietry application that my company uses, pumps data into the tables via an ODBC link. Unfortunately, it cannot also insert the current date (ie. the date on which the data was ...more >>

Hi, is there a way to use windows environment variables in a MSQL script ? I want to write a batch to backup/restore a database and want to get the path from an envvar. Because it's a multiline command I can't do it on the prompt (osql -q "xxx"). Or am I wrong ? Thanks in advance Celphor...more >>

Is there a way to pass a variable to a View and then base the select statement off of that? I have some complex queries and would to be able to reference a column from a view as if it were an existing column. The only problem is that I need to be able to pass at least one variable, the date. Plu...more >>

I'd like to have the option to pass in 3 strings to a stored procedure. I may have only 1 or 2 string that I can pass in at any given time. I'd also like to structure my select statement differently depending on whether I have 1, 2 or 3 strings passed in. I tried to check for null in the st...more >>

create #table(A nvarchar(30), B nvarchar(20), c nvarchar(15), D int) insert into #table select a,b,c,d from x,y,z where c like 'Norm%' group by a,b,c,d create #table(class,name, exam,score) as: A name1 math 100 A name1 math 88 A name1 Phy 98 A name1 Chm 98 A name1 SPT 89 A name2 ma...more >>

create #table(A nvarchar(30), B nvarchar(20), c nvarchar(15), D int) insert into #table select A,b,c,d from x,y,z group by a,b,c,d select *,(select max(d) from #table t where t.a = d.a and t.b = d.b and t.c like 'm%') as mx1 drop #table go when there is such data as t.c like 'm%', i...more >>

create #table insert into #table select A,b,c,d from x,y,z group by a,b,c,d select *,(select max(d) from #table t where t.a = d.a and t.b = d.b and t.c like 'm%') as mx1 drop #table go when there is such data as t.c like 'm%', it works OK but otherwise, i get error: not supported, ...more >>

got a table with (class, name, exam, score) fields. To find max(score) for exam that starts with letter 'm' grouped by class and name,did: select max(score) from #temp where exam like 'm%' group by class,name this work only for selecting max(score) from #temp. How to select all columns a...more >>

Declare @mx int Set @mx = (select max(score) from ,,, where ,,,,and substring(city,1,1)='g') select score,@mx from ,,,, where ,,,, and substring(city,1,1)='g') group by score go this seems to work for small tables but when table is big and substring(city,1,1)='g') condition is not...more >>

Hello, I would like to start a sql query file from inside another sql query file. Is this possible to do this? Today I have a very big sql file (more than 60000 lines) and I would like to split it up into many small files to make it easier to maintain the sript. A main sql file sould then check...more >>

name1 field is is nvarchar(40). (1)select case when isnumeric(name1) = 1 then cast(name1 as int) else null end as name In (1) when name1 is not numeric, name is null and its type becomes number not string(40). And, (2)select case when isnumeric(name1) = 1 then cast(name1 as int) els...more >>

Table (continent,country,city,district,score) a aa a1 g1a 100 a aa a1 g2a 99 a aa a1 g3a 99 a aa a1 f4d 89 a aa a1 f43 77 a aa a1 f4f 88 a aa a1 f4s 88 a aa a1 h34 67 a aa a1 h35 56...more >>

Hello im new here, but i was wondering if anyone can help. here is the select portion of my statement: SELECT tblContract.RemainOptions, tblContract.CurrentExpirDate, tblContract.NoticeLength, DateAdd('d',-tblContract.NoticeLength,tblContract.CurrentExpirDate) AS NoticeDate, IIF(tblEmplo...more >>

Hello -- I have about 500 user stored procedures that I need to GRANT execute permission to one user. Is there an easy way for me to grant permissions in one shot. EM requires me to click on each SP which requires about 2-3 mouse-clicks. Any script or shortcuts would be appreciated. ...more >>

SQL gurus, Simple question: Our database is 470MB and transaction log has become 18GB! (Clustered). I see local Fixed drives in both the SQL boxes from 'System Information' and disk manager where data is being stored. We are backing up database once in 24 hours and transaction logs every ...more >>

SQL gurus, Simple question: Our database is 470MB and transaction log has become 18GB! (Clustered). I see local Fixed drives in both the SQL boxes from 'System Information' and disk manager where data is being stored. We are backing up database once in 24 hours and transaction logs every ...more >>

Hi My SQL 2000 server has reached an incredible size in one of it's transactionlogs. 2GB for a 5mb database.... And it is filling the entire harddrive. Kan someone help? - Martin...more >>

Hi, I have the following data: ID EquipmentID DateTime On -- ----------- ----------------------- -- 1 1001 2003-10-02 14:13:15.000 1 2 1001 2003-10-02 14:15:49.000 0 3 1002 2003-10-02 14:20:45.000 1 4 1002 2003-10-02 15:13:15.000 0 I am trying to write some...more >>

I have three tables: Table Race RaceID RaceDescription ======= ================ 1 100 Yard Dash Table Racer RacerId RacerName ======== ========= 1 Freddy 2 Joey 3 Iggy Table Lap LapId RacerId RaceId LapNum LapTime ===== ====== ====== ====== ====== 1 1 1 1 32.44 2 1 1 2 3...more >>

Hi; I'm trying to copy a database and each time it gives me an error on copying the database file itself. Neither computer is on a domain. Is that a problem? thanks - dave Windward Reports -- http://www.WindwardReports.com DefendTek -- http://www.DefendTek.com Page 2 Stage -- http:/...more >>

I am running SQL 2000, and my server crashed. I have the caseworker.mdf database file but not caseworker.ldf transaction log. How can I get SQL to pickup the database again without the transaction log? Everytime I try to attach the database it wants the Transaction log. ...more >>

I have a database I would like to get working. The only problem is I do not have the Transaction log. For instance the database is case.mdf and the log file would be case.ldf. Well I have the case.mdf and that is it. How can I get SQL to pick up this database to access? ...more >>

Hi; SQL Server developer edition on my desktop (WinXP Pro) works fine. On startup ERRORLOG has: .... 2003-10-02 09:27:09.93 server SQL server listening on 199.45.247.107: 1433. 2003-10-02 09:27:09.93 server SQL server listening on 127.0.0.1: 1433. 2003-10-02 09:27:10.37 spid5 Cle...more >>

Hi, I'm trying to do a following query. select * from mainTable mt, subTable st where mt.name = 'something' and st.PK = mt.PK It gives error: Invalid column name 'PK'. But I've seen examples in BOL that use PK as primary key. Is it that it can be used only in some special cases or wha...more >>

Hi all, What's the performance difference between having one index on 2 fields versus having 2 separate indices on those fields? In which case should I use one but not the other? Thanks in advance, -Oleg. ...more >>