I have two tables of book information. One that has descriptions of the book in it, and the isbn, and the other that has the book title, inventory data, prices, the isbn. Because of some techncal constraints I won't get into now, I can't combine them both into one table. No problem. Things a...more >>

Hi, I'm trying to get the count of rows from the union of several tables. My code is: select count(*) from (select * from #AdvSearch_Main union select * from #AdvSearch_Atty) This will not get past the syntax check saying that the error occurs on the final closing ")". Can someone...more >>

SQL Enterprise Manager Version 7.0, Windows XP professional version 5.1 The list panels (right half of the screen) will not show the tables. The top of the panel reads: 245 items, which is correct, but the panel reads: there are no items to show in this view. This error is occurring on ...more >>

I am not sure why this is producing a SQL Server related error, but w/o having an instance of SQL Server on my machine to verify anything further, can you all help me with this? <!--- validate() ---> <cffunction name="validate" access="remote" returnType="numeric"> <cfargument name="use...more >>

Table1: SessionID int Hours int .... SessionID Hours 111 3 222 2 333 3 444 2 Table2: SessionID int RegistrationID int .... RegistrationID SessionID 888 111 888 444 777 111 666 ...more >>

Is there a way that I can Automate SQL Setup via C#, PERL or VB?...more >>

To all SQL gurus: I have a Windows Service that uses a single SQL Server table to retrieve items of work. Each thread of the service checks this table for the earliest item of work that is not already in process, marks that item as in process, then begins to work the item. My concern is wheth...more >>

Does anyone know of any Windows software to scan the same paper document and enter the results to an SQL Server database? We have a Photocopier machine that can automatically scan several documents at a time and email them to an email address. We'd like to scan in an assessment form that rates...more >>

I am fairly new to SQL Server. I am writing a tool in stored procedure to identify locks in a table. I have already written the basic framework of the SP. It will reside in master database and take two inputs. Database name and table name. From that it will show all locks at that instant on that...more >>

We have 2 Windows 2000 Servers, Server2 and Server3. Server3 also hosts a SQL Server 2000 Instance. Server2 DOES NOT host any SQL Server 2000 instances and is used as our Application Server. We are trying to stop a "service" that is setup on Server2 by executing a DTS package that resides in SQL...more >>

Ciao ho una tabella in sql server formata da questi campi società, valorefattura, datafattura Vorrei estrarre i dati in questo modo anno anno -1 anno -2 Società somma(valorefattura) somma(valorefattura) somma(valorefattura) Potete aiut...more >>

On the subject of Data Warehouses, Data Cubes & OLAP&#8230;. I would like to speak frankly about Data Warehouses, Data Cubes and OLAP (on-line analytical processing). Has it dawned on anyone else that these buzz words were created by some geek who decided to take a stab at marketing? Knowin...more >>

Given the table (mytable) my_id int (pk) my_type char(1) my_version tinyint my_datetime datetime Example data 1 a 1 1/1/03 2 b 1 1/2/03 3 c 1 1/3/03 4 d 1 1/4/03 5 e 1 1/5/03 6 a 2 null 7 b 2 1/5/03 8 c 2 null 9 d 2 1/5/03 10 e 2 1/6/03 I want to write an update stateme...more >>

Hi, I want to find out the project names, for the PROJ table and I know the Project IDs as a string (comma seperated). I tried this: --------------------------- DECLARE @proj_ids varchar(500) SET @proj_ids='(100,200)' SELECT PROJ_NAME FROM PROJ WHERE PROJ_ID IN + @proj_ids --------...more >>

The transaction log in a database in our SQLSERVER-2000 server has grown to 16GB. I cannot shrink the transaction log manually because it says that the entire 16GB log size is not free. This is strange because we backup the transaction log every hour, and that should have truncated the transacti...more >>

Hi How do you reset the login id and password for ms sql 2000 i.e. back to sa, sa thanks...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 >>

Hi! I have a scalar function that returns integer: xview (int) Now, I'm trying to build a procedure that has the following select inside: select atr1, xview(atr2) from tablename But, I get the 'Invalid name' error when I try to execute that procedure. If I got it right, I must use...more >>

Dear all, Our application needs a bit of database redundancy. Our application only accesses database for reading purposes. We want to have two databases in separate machines. Incase one database server dies the application should automatically extract data from the other ser...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 >>

Hi All I have a table in SQL Server with ID having indentity inrement by one. Table has not any trigger. Frequently ID in the table jumps. Any help !!! Thanks...more >>

I'm trying to concatenate fields in SQL stored proc for use in text field in asp.net dropdownlist. I'm running into a problem when I try to use a DateTime field, but can't find the answer (so far) on the Internet. Was hoping someone here would know? My sql stored proc: SELECT AnomalyID, Dat...more >>

I posted this in the MS Access group, but no luck. ------------------------------------------ I've got another stored procedure in the same app that returns multiple recordsets and the code works. But now I've written another SP and the code traps out with the 3251 message. The SP is writin...more >>

We are running a DTS package with the dtsrun utility and would like to pass a variable through it. Inside our package we have a VB script that references a table that contains the information about all of our jobs and we would like to pass a variable to tell it which jobs to run. The reason we...more >>

Hi there! I've got a problem using ADO to execute a stored procedure (sp) on sql server, which gets two dates and fills a table with some data on that interval. Typical execution time is about a minute for a 30days interval. Since the sp takes potentialy a long time to execute, I executes it as...more >>

I was wondering if sql server 2000 has an auto documenter utility that can dump information about a database into a word, excel, or text file. I would not need anything to complicated just table, fields, types, sizes, and maybe indexes. Currently I am just exporting my tables into access, and...more >>

Hi, SQL Server 2000 SP3 Windos 2000 Server SP4 I have a DTS package that imports data from a dBase IV databse with files located in two folders (dBF1 and dBF2). I use a transform data task to transform the data. They were running properly, but last week we installed W2K SP4, and now the...more >>

Has anyone come across a change tracking tool for SQL Server. Specifically the scenario I want is the following : A Production DB needs some modifications to its content. This tool will copy the DB to a dev environment. The Dev environment will be 2 copies of the DB, 1 as a control set and the...more >>

Firstly, sorry for the long post, but I've included a fair bit of sample data. Im doing a comparision of 10yr Bond prices and CPI adjustments, with an 18 week moving average of the CPI. I'm using a nested sub-query to calculate the moving average, but I'm having difficulty selecting exactly 1...more >>

hi, im working on this for a long time. i'm using MSsql-server2000 i have a table [visits] that records users visits to rooms. the columns are room_id, user_id, visits. i want to write a query that can calculate the top 10 rooms that are related to any given room. i was thinking of firstly makin...more >>

We recently upgraded our SQL 7 servers to 2000, and we have a good deal of distributed queries in the form of Stored Procedures that run between them throughout the day. After the upgrade, I began getting the "CONNECTION BUSY PROCESSING RESULTS FROM ANOTHER COMMAND" error. This is intermittent ...more >>

Hi out there! My publisher database holds an table with an column that is to small. The related subscriber-table I already have expanded (column from varchar50 -> varchar60). But I can't expand the table in the distributer-base, i get the error that it can't be done because of replication. ...more >>

I've got some SQL that works as far as returning a recordset from a series of UNION statements. viz: SELECT whatever UNION this UNION that UNION other Now I want to group and sum on it's results. Started out tying: SELECT * FROM ( union stuff ) ....but couldn't even get past...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 >>

Hi, One of my clients has the following situation. They use Access DB for data update etc. some business functions while they also want to view the Access data quickly and more efficiently in SQL Server 2000. Huge Access db with over 100 user tables, over 60 MB data. The DTS package that c...more >>

Hi, I have the following SQL SELECT Table1.Col1, Table3.Col1 AS Expr1, COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc, FROM Table3 INNER JOIN Table2 ON Table3.Col1=Table2.Col1 RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2 GROUP BY Table1.Col1, Table3.Col1 The outpu...more >>

I have two databases A & B, I copied all of the tabels and data from A to B. I can't figure out how to copy the Views and Stored procedures from A to B. As you can see I'm new at this, can anyone point me in the right direction? Oh.... forgot running SQL 2000. Thanks...more >>

I'm sure this is simple -- I'm just spacing it today. Assume the following: freq fruit ---- ---- 10 apple 5 apple 7 banana 6 pear 3 pear 2 orange 8 orange 13 kiwi How do I find the highest [freq] for each [fruit] all at the same time, e.g.: ...more >>

Hi. I'm currently working on a project which involves the creation of a web page which reports selected data to customers from two back-end systems. I use ASP script language on IIS, the server is MS SQL 2000. Now I'm struggling with combining two tables from the different databases. I'm sure...more >>

I'm putting a little table in my DB that I'll call zstblVersion. ("..stbl" for System Table, "z" so it sinks to the bottom of the list and doesn't look like something to do with the app) The purpose is to let somebody look at the DB and divine which version they're seeing. So far, I've go...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 >>

I have a very large array of floating point numbers which I am trying to store in an image type field. I am using the dataset class to directly enter the data but am having trouble trying to get this type into the image field. I guess the question is how do I get a large floating point array ...more >>

How do i setup the table so that the default date enters the current date whenever someone enters a record. vj...more >>

I am having a problem restricting write access to tables in my database. In my database I have a table called, for the sake of argument, 'TableX'. In my SQL Server Logins, I have set up a login for 'Domain Users' using NT authentication, and a login called 'FullTableX', using SQL Server auth...more >>

Hi ! Hi ! I'm thinking of installing a Microsoft SQL Server 2000 on a database server, to be the db of a webapplication, but the it must be possible for the users to copy/replicate data from the server db, to a local db on their e.g. laptop to work even if not online. Is there a tool that can...more >>

Hi I am trying to do a simple select using a date value. For eg:- in oracle i would do the following select count(*) from TEMP_TABLE where to_char(modif_time,'mm/dd/yyyy')='10/04/03' How do I accomplish the same in sqlserver? Thanks in Advance sk...more >>

Hi, i have a problem about the CLIENT-SERVER architecture procedure. Well , i have an application in VB with ADO connection to a table in a database on a SQLSERVER 7.0 . Is possible to do that when a client updates a data in a field of my table , the SERVER communicates to all clients connected...more >>

sql server 2000 8.00.194 Microsoft Windows 2000 Service Pack 2 I looked into my sqlserver agent log and found the following ODBC Error 0 Timeout expired[SQLSTATE HYT00] logon to server local '( failed)' (Job manager) I have no idea what that means but it did keep out users for a minut...more >>

I have an application that needs to execute dtsrun from a remote machine. Basically I have a batch file on the remote box, which handles the execute. This works fine on a machine with client tools installed. My problem is that I can't install the client tools on the remote box. Does anyone kn...more >>

I have a procedure that take several paramters and depending of what values is submitted or not, the procedures shall return different number of rows. But to simplyfy this my example use just one parameter, for example Idnr. If this id is submitted then I will return only the posts with this ...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 >>

I want to build a system that will have about 1 million rows in a table in sql server database.I am using this for a web application and accessing it via JDBC type 4 driver.But display 20 records at a time only using pagination(as in google).What will be the best way to go about this....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 >>

Hi all, I have two databases DB_External and DB_Internal. I am writing some stored procedures (in DB_Internal) that reads from the tables in DB_External. I execute my stored procedures from DB_Internal. Everytime I want to read the tables in DB_External, I have to refer to the table as D...more >>

Hi I have written this query for generating a report. select * from shrcertmaster left outer join shrsharemaster on shmacno = ctmacno This returns the output given below. CtmCoy CtmAcNo CtmCertNo CtmBenf CtmBatch CtmShare UB 1234567905 123453 123476 0001 1000 UB...more >>

Hi, I want to create a temporary table and store the logdetails from a.logdetail column. select a.logdetail , b.shmacno case when b.shmacno is null then select cast(substring(a.logdetail,1,charindex('·',a.logdetail)-1) as char(2)) as ShmCoy, cast(substring(a.logdetail,charindex('...more >>

I have a transaction log file which is 29 GB in size. I am trying to reduce it by using dbcc shrinkfile with truncate_only but, it has no affect on it. So, I tried to look at the logs by running dbcc loginfo (dbname). The last log is what's active (status is 2). How do I make this status to ...more >>

Hi folks, I want to be able to store and retrieve UNSAVED Word documents as BLOBs. I got all the info for storing them if they're already saved on the file system. But what if they're not already saved? I could save them to a temp file first and then make it a blob, but I'd rather not put them...more >>

Hi I'm not sure what the best approach for this is: I have a stored procedure which I would like to use to return several output values instead of returning a recordset. CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int OUTPUT) AS SELECT field2, field3 FROM Table WHERE ...more >>

Hi folks, I'm doing calculations based on data in a table, but the data has some zeros in the field I'm dividing by. I'm trying to write a script to replace any field with 0 or null with 1, but it's not working. HEre's what I've got: Update A Set A.deptcode = A.deptcode, A.type = A...more >>

Hi folks, Hopefully this is a simple fix, but I keep getting Syntax error with this statement in an MS SQL DTS statement and in Query Analyzer: Update A Set A.deptcode = A.deptcode, A.type = A.Type, a.TotalExpenseUnit = (a.LaborExpenseUnit + a.OtherExpenseUnit) Where a.Type in ...more >>

hello everyone, we dropped the clustered & nonclustered indeces on a table, then rebuilt them. logical fragmentation is near zero, but extent fragmentation is about 40%. how can this be if the indeces are brand new?...more >>

What is the best way to compare two entries in a single table where the two fields are "almost" the same? For example, I would like to write a query that would compare the first two words in a "company" field. If they are the same, I would like to output them. For example, "20th Century" an...more >>

Hi all, I have a stored procedure that return a resultset e.g. stored proc: get_employee_details select emp_id, emp_name, emp_salary, emp_position from empoloyee I would like to write another stored procedure that executes the above stored procedure - returning the same number...more >>

Hey guys, I was curious how other people create several 100 SQL objects on a new database. Example: I have each procedure in its own text file (IE 100 .sql files), so I just check them out of source control, then run a perl script that just dumps them into one txt file. Then I paste that m...more >>

Hi all, I need to update some data in a table, based on some criteria. In this case we are talking about the stamping of a price against a job. The update table holds the jobs, and the update_details table holds the activities performed on each job and the cost for each activity. If i pull b...more >>

I have a SQL Server 2000 that failed to start after a Windows reboot. Agent properties are checked to auto start if stopped. Under the Agent Error Log, I have the following data: Information: SQL Agent stopping because of Windows shutdown. SQL Agent terminated normally. Error: SQL Server ...more >>

Recently, I will write a database application to get the median value according to the grouped condition. maybe, just like the following. SELECT Max(a1) MaxValue, Median(a1) MedianValue FROM test_table Any suggestion? ...more >>

Hi, I have a linked server connection from SQL SERVER 2000 to Sybase 11.9.2 and can see all the tables and views in sybase database. But, when I try to access a table from MSSQL SQL query analyser it is not able to access one single table and gives the following error.... I am a newbie in SQL20...more >>

There are two tables, parent and child table. Foreign key field of the child table is not a required field. If the data is submitted w/o a value into the foreign key field, what should I make it as a default value? Should it be "null"?...more >>

I have a situation that some of you may be of help. I will name the tables specifically so you can understand what I am talking about. I have a COS_species table....the information in it cannot be changed. Everything in this database is pretty much centered around the speciesID. We also wan...more >>

My client will be receiving a .dbf file which needs to be uploaded into a sql server database table (as an append) every week. They are NOT computer savvy and I would like to automate this process rather than go into enterprise manager and run data transformation. Is there any way to write a ...more >>

Hi all, I did a search for a stored procedure that would give me the table sizes and space used for each table within a database and found this in the archives ‘exec sp_MSforeachtable 'exec sp_spaceused ''?''' The sp_spaceused I found in the help but where is the sp_Msforeachtable? How does...more >>

Greetings, I asked this question over on the ADO.NET newsgroup and couldn't scrape up an answer. I realize that it is more of an ADO question than an SQL Server question, but I'm hoping there might be an ADO programmer here that can explain this to me. I'm developing database applicatio...more >>

Hello Does anyone one know the max size of a database that I can create on MS-SQL server 7. Have had any problems using databases near the max size (slow performance, etc) Thanks Helena...more >>

SCENARIO: SQL Server 2000 Back End. The customer calls in with a problem and a QUERY is raised. The date of creation of the record is in the fldDateQuery column. The user responds to the customer and a RESPONSE is raised. It contains the ID of the associated QUERY and the date of creati...more >>

My company is considering purchasing MS SQL Server to run an application on (SASIxp). I am mainly familiar with Oracle, so I was wondering how long it would take to copy a database. Basically we have database A and each night we want to replace database B with the contents of A. How long would t...more >>

From another post I was given a solution to a problem I was having with creating a composite view of similiar rows. http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&threadm=f01a7c89.0310070756.6434ecfd%40posting.google.com&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26group%3Dcomp.database...more >>

Hello all, We need to reverse engineer a very large application written in PowerBuilder / SQL Server to port to .NET. We have only binaries of application not source code. What are tools / log facilitites / spy appliations that shall allow us to see exact SQL that is being carried by driv...more >>

Hi, I would like to write a stored procedure that returns a resultset. e.g. select student_id, student_subject, subject_marks from student_results where student_class_no = ? The input parameter is student_class_no (see ? as per above SQL). The output is a resultset, with eac...more >>

Hi, How can I create a temporary table say "Tblabc" with column fields ShmCoy char(2) ShmAcno char(10) ShmName1 varchar(60) ShmName2 varchar(60) and fill the table from the data extracted from the statement... "select logdetail from shractivitylog" The above query returns single ...more >>

When a trigger on one table updates another table, does it activate the update trigger on the updated table? For ex: 1. Table A has a trigger for update 2. Table B has a trigger for update that updates table A Will the trigger no table A run when table B trigger updates table A? Than...more >>

Is there any SQL Error? Or I have to use Select case in VB code to control SQL instead. Thank you for any ans. Nuno...more >>