Hi, I am having a hard time setting up an SQL Server 2000 Developer Edition instance for remote clients. The instance is running well and I can connect to it on the local computer. When I try connecting from another computer using Enterprise Manager with the server's IP as the instance name...more >>

Im trying to find the error in this statement: CREATE PROCEDURE STP_selectmain AS select a.inventoryid, b.firstname, b.lastname, art.title, art.medium, a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference, art.provenance, sum(c.restorationcost), sum(d.framingcost...more >>

For some weeks now, SQLServeragent hangs after I restart the computer. What could be a reason for this? I don't think I changed anything. The system protocol only says "SQLServeragent hung during start" (or similar - I have a German speaking system here). The application protocol says someth...more >>

Hi all, I have a .BAK file which i need to restore. It originally came from another machine and I am now trying to restore the DB. When i try to restore the bak file using enterprise manager i receive the following message... "The backup set holds a backup of a database other than the ex...more >>

I'm running a DB using MSDE (2000) that is interfaced by 2 different ades running on PCs with Access 2000 Runtime. One of the ADEs is a package accounting system that is very solid and stable, the other is a custom application that I wrote (much less solid and stable). The custom app only deals...more >>

I have two tables Income(Date, IncomeType, Amount) and Expense(Date, ExpenseType, Amount). I want to create a balance sheet report for which need a query or view which creates a virtual table of form BalanceSheet(IncomeType, IncomeAmount, ExpenseType, ExpenseAmount). The table should look li...more >>

Hello: I have an application (using ADO 2.7 - written in VB) that access a SQL Server 2000 Database. Every once in a while, the program hangs when I am accessing the DB, and sometimes I get a timeout expired error. The wierd thing is, when it hangs, if you try the function again - it goes ...more >>

Ive seen several sellers, both retail and on ebay, who say that sql server 2000 developer edition will run on windows xp home edition. Is this true? Some articles I've seen imply yes while other articles imply no. I'm trying to learn sql server and a lot of books I've purchase offer a 120 da...more >>

I know I must be missing something pretty obvious, but what setting should I change to get the correct length of my string ie why does the select statement below return 1 when it looks fairly obvious that the second string is a tad longer than 1 select len('a ') select len('a ')...more >>

Ok, folks, a question for you. I wanna send email through MSSQL Server 2000 using SMTP protocol.HOW? ...more >>

I have a task to where I need to move a column from one table to another. I want to be sure I update any view, stored procedure, trigger, etc. that references the column. I simply want a query that will report the related objects and then I will update them manually but before I go and try and f...more >>

I have created a view by using following statement:- ALTER VIEW vw_ctotalweeks AS select DISTINCT allweek from hr_depts WHERE allweek > DateAdd(day, -28, GetDate()) order by allweek desc But I am getting follwoing error message:- The ORDER BY clause is invalid in views, inline functions,...more >>

Greetings, Would be thankful for your kind help. I have a table (Northwind employees table). In the employees table, a column called ReportsTo which lists the EmployeeID, int data type, of the manager of an employee.This ReportsTo column is basically the EmployeeID column: EmployeeID Ti...more >>

Greetings, Would be thankful for your kind help. I have a table (Northwind employees table). In the employees table, a column called ReportsTo which lists the EmployeeID, int data type, of the manager of an employee.This ReportsTo column is basically the EmployeeID column: EmployeeID Ti...more >>

Hi, I have been working as Oracle dba for quite a few years. But now my company is been getting a couple of projects relating to sql server which also have to be supported by my team. Could anyone suggest how to start with ?? may be couple of web links which could be helpful or some pdf's ...more >>

Hi All Im generally a vb programmer and am used to referencing multiple records returned from a query performed on an sql database and im trying to move some functions of my software into sql stored procedures. So far ive been able to move the functions relatively easily but im unsure about ho...more >>

I have function that returns a table of information about properties. The data comes from three different tables -- addresses (called PropertyID), property characteristics, and events concerning those properties (sales, appraisals, etc.), plus a table that maps one representation of property ...more >>

Hi everybody, I just wrote my first two triggers and from the minimal amount of testing I have done, they work! However, I was hoping I could get some feedback from those of you more experienced in writing triggers. Here is the first one: CREATE TRIGGER DecreInters ON Interaction FOR DELE...more >>

Hi, recently we increased our internal memory from 2 to 3.5 GB. Nevertheless, SQL Server does not seem to use this extra memory. Do we have to do something extra ??? Arno de Jong, The Netherlands. ...more >>

When I try to remove a table, it complains because it's part of a publication. I could script the publication and subscriptions such that they can be deleted, the table removed, and then added again. But, I not sure if this is the 'best' approach. Anyone faced this challenge before? Regar...more >>

I have a table (in Access) that is linked to the SQL server, and I need to add a column to it. I wrote the following: ALTER TABLE Census ADD COLUMN 'ActiveFacility' BIT; and I get a syntax error that I do not know how to solve. The column needs to contain yes/no data for each record. Any...more >>

I am looking for a book that discusses query tuning, index tuning, execution plans, etc. Can anyone recommend something? -- BV. WebPorgmaster - www.IHeartMyPond.com Work at Home, Save the Environment - www.amothersdream.com ...more >>

I have been using the index tuning wizard to review some of my stored procs, and views. So far most of my indexes have been set up well, but I am curious as to how they would look under a production system load. I was thinking of running a profile for about 30 minutes or so on the prod system, an...more >>

Hello, i ahve a problem to connect a pc to another with MSDE installed. The response message of this failure is: 'Server not exists' Can help me? Thanks!...more >>

I have a query which filters records containing uppercase and Lowercase i.e. Smith and SMITH, Henderson and HENDERSON etc. Is there a way that I can filter only those records that contain the first uppercase letter and the remaining lowercase letters for my query i.e. Smith , HENDERSON etc....more >>

Objective: The primary table I loaded into MySql has 2.5 MM records: ID, Ticker, Date, Price; and all works well. My need is to write a QUERY to export outfile?) multiple text files. For example, I have 6 years worth of data, and need to generate 1 file per day that contains all the Tickers and...more >>

Hi all, I have a very old MSSQL data backup file. I do not even remember that it was created in SQL7 or 2000. Anyway, I'm trying to recreate DB from this backup. Is it possible to create DB from backup file? I tried restore from that file and SQL server complaining. "The file on device '**...more >>

Is there a utility that will allow me to see what users on my network are connected/active on SQL server? Ryan ...more >>

Situation: I'm relatively new to mysql, but successfully installed, connected, and loaded my resepective Access tables to the server. I also installed EMS Mysql Manager as a UI to help the learning process. All works well, and I'm impressed with the speed and flexibility to MS Access ~ albeit ...more >>

I have several instances of "magic number" variables (tinyints). In my program, I have assigned an enumeration to make the meaning clear, such as: enum Condition { Green = 0, Yellow, Red } In my database, one of the tables contains a "Condition" field (tinyint), which stor...more >>

Hi All Database Gurus, I am trying to write code which will produce all the possible valid queries, given tables and join information for tables. Right now i am just trying to construct all the sequential joins. eg. if i have 4 tables A, B, C, D and the join conditions are A Inner join B,...more >>

Is there any chance to get the content of a timestamp field? I am trying to get the content via Visual Objects and I am receiving only nothing. Not NIL but "". Is it possible to get any useful information out of this field in any language? (btw I know that the MS-Timestamp is not a timeformat)...more >>

Hi all Would there be a easy way to find the column name(s) which constitute a Primary constraint for a table through navigating the system catalogs. I found that the PK Constraint object in syscontraints is showing the colid = 0. TIA Norman...more >>

Hello, Quick, and possibly strange, question. I am doing some work testing the running time of some dynamic SQL statements on a remote machine. What I would like to do is execute the SQL on the remote machine, without returning the result set to the calling machine (this would skew the res...more >>

Hi guys, would appreciate if you can shed some light on this. Sorry to be a pain, can you tell me what is wrong with the following: for /F %%i in ('dir /b /on c:\bcp\pc*.txt') do bcp Inventory..pc in %%i -fc:\bcp\bcp.fmt -T -S CHICKYy where CHICKYy is the server bcp.fmt 8.00.194 6 1 ...more >>

I want to restore a huge database into my workstation. The size of the backup file is more than 6 GB and I don't have enough space on my HD for both the database and the backup file. So I put the file in a shared folder on a pc connected through a switch to my pc. My wkst uses w2k pro sp4, ...more >>

Hello everyone, hope someone can help me with this. I have a SQL stored procedure that inserts a record into a table, creates a cursor to fetch the last record that was added to get the unique key that was created and then writes that and other info to a separate table. This procedure was w...more >>

We need to present hierarchical data on a web page, the same way the tree view shows files in Windows Explorer. Here's the catch: that tree view needs to be bound to a SQL Server database. How can this be done?...more >>

Before going in to details i like to understand more about concept and design of this database. Most books go directly in to details. Please, can a current admistrator gife me information on this? Thanks in advance, Dick vd Spek email: ds@dds.nl...more >>

Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and includes the following columns: DocID (INTEGER, PRIMARY KEY, CLUSTERED) IsRecord (INTEGER, NONC...more >>

Hi, We're using SQL Server 2000. A very basic query on a 5 million row table would not work. If we hard coded the one input parameter, the query used the foreign key index and produced an instantaneous result. If, however, we pass the parameter in as a parameter, the query doesn't use ...more >>

How to get the date a view was last modified? (As opposed to created) ...more >>

We are having quite a time since moving a large database to a new server (actually built new server, renamed as old to make seamless for users, etc.) Import 104 million row database (5 column) into table (CD_Assets_bad2) from existing (CD_Assets): Account(varchar(8)) TransactionDate(datetime...more >>

I am trying to use a command line program to run a stored procedure that generates output in a comma-delimitted format. Somehow, ISQL or OSQL always wrap the lines at 256 characters. I believe this has something to do with the column width switch (-w). But enlarging the column width to 800 chara...more >>

I created a view that joins two tables (tblOrder and tblProduct). The field ProductID is common to both. When I save the View I get the following error message: "Column names in each view must be unique." Is there a way around that? Should I be naming my Key Fields and Foreign Key fields differen...more >>

Hi gurus, any help would be really appreciated. I know this is probably easy for you, but what I am trying to do is do the following: I have 120 computers to support and I have generated the following type text file recording some inventory stuff for each pc. pc1.txt Computer Name, PC01 I...more >>

Hello, I have several SQL Server jobs that execute procedures written to run deletes and updates against tables in my data warehouse. Some jobs occasionally get stuck with the status "Performing Completion Actions" and can only be reset by restarting SQL Server or rebooting the server. I h...more >>

We have a list of companies in our SQL Server 2000 database. We would like to graphically show the parent companies and subsidiary companies. I can create a query that will list this information, but is there a tool that can show this graphically?...more >>

I'm writing a stored procedure that uses DynamicSQL using the following code: SET @Get_Role_Number = 'Select @Role_Number = ' + @DatabaseName + '.dbo.sysusers.uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' + @DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRole Execute sp_execut...more >>

Anyone here tried ListCleaner by a company called WinPure http://www.winpure.co.uk/lists (a data deduping software) in comparison to other products that may be out there? Looks like some companies like Hewlett Packard use this. I am looking for a good (and inexpensive) datacleansing tool to ...more >>

SQL Server 7.0 If I run the following in Query Analyzer I get no records returned: exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183' If, however, I run either : exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176' or exec GetLeadsOutcome_Dealer '1/1/2003','1...more >>

Hi all, I have a huge excel format file wants to export to sql server database. One of the field has combination of numeric and alphanumeric. When I import the excel format to sql server database, the numeric is successfully imported but the alphanumeric does not import at all. Does anyone...more >>

Is there a GUI to do like drag and drop and very powerful , but not DOT NET dependant. I have found some programs that are GUI rich in scripting for SQL 2000 and even MY SQL, but they are dot Nut,net dependant. michael...more >>

I have many columns of data. They are all using the money datatype. When I enter a value into a column such as 32.00, it only shows up as 32. When outputted to an ASP page using a query it also only displays 32. If the data is 32.33, then the display is 32.33. How do I get it to display 32.0...more >>

SQL Server 2000 - MSDE 2000 Is there a way to get the number of current users logged into a SQL 2000 = Server (also MSDE)? Cant be distinct users as most users are logged into = the database using the same login. --=20 Tim Morrison --------------------------------------------------------...more >>

I'm using xp_cmdshell to output a text file from a trigger like this CREATE TRIGGER [CreateFile] ON tblApplications FOR INSERT AS DECLARE @FirstName varchar(75) DECLARE @LastName varchar(75) Declare @strcmdshell varchar(150) Declare @filename varchar(100) Declare @filecontents varchar(...more >>

I am trying to loop through a string of ID's and check if the productname for the ID is 'Chai' if so change it to 'Tea'. This is in the northwind db - products table. call it exec sp_UpdateProduct '1,2,3' It does not work, any ideas - syntax, etc.... CREATE PROCEDURE dbo.sp_UpdateProduct ...more >>

Hello, I just installed SQL Server 2000 on my home computer and am connecting to a remote SQL server over the Internet. I can log into the server just fine and see 7 folders including the 'Databases' folder. I can open up all of the other 6 folders but for some crazy reason when I double click o...more >>

Greetings, My SQL 2000 Server has been acting strange lately. Someone has gone into the server and changed the SA password. When I go to change the password back to sa, i then verify it and it seems OK. When I go to look at the properties again, the same unknown password pops in place. This ...more >>

Hi Dose any body know why a temporary table gets deleted after querying it the first time (using SELECT INTO)? When I run the code bellow I'm getting an error message when open the temp table for the second time. Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E37) ...more >>

I haven't a clue how to accomplish this. All the data is in one table. The data is stored by registration date and includes county and number of students brokne out by grade. Any help appreciated! Rob...more >>

We have a SQL 7 server SP3 which we want to replace. The initial plan is to create a new SQL 7 server and restore the database backups. My first question is there any need to restore the master database once all the other db's are copied. We had planned to copy the databases and the msdb. Do...more >>

Could someone assist with getting the count function working correctly in this example please. I know the count function will return all rows that do not have null values, but in this case I want to count all the rows except those with a zero sale price, (which are unsold). The table shows wor...more >>

I have been asked to prepare a feasibility study for an upgrade to a VB app that uses a SQL server db. The application is thin client, with about 100 sites connecting via terminal services. 99% of the processing and business logic is done using stored procedures. The application is a highl...more >>