I am on windows xp, have installed sybase client, what driver do i need for tcp/ip connection to sybase12 sql server? For odbc, I only have Microsoft sql server driver Version 2000.81.9042.00...more >>

SELECT ISNUMERIC('. ') 1 SELECT CAST('. ' AS [insert any numeric type]) Syntax error converting the varchar value '. ' to a column of data type [insert any numeric type]. Any thoughts as to why SQL Server 7.0 considers '. ' to be numeric, yet can't convert it to any numeric data t...more >>

Hi, I wanted to know how can we send out email notification for the Jobs that are completed. Thanks a lot in advance.... AJ...more >>

After a recent crash, where tables had to be repaired we have been unable to re-instate or create new merge replication from a MS SQL 2000 system. All old publications and subscriptions have been deleted. New replications fail at the snapshot level. Anyone ideas ? *** Sent via Developersdex ht...more >>

I have two tables 1)Rollout_detail start_date Datetime, contract_date Datetime, budget_amt Money store_id int(foriegn key referring store.store_id) pan_number varchar(20) roll_id int 2)store store_id int(primary key) skey varchar(10) ...more >>

I have my Sql 2000 maintenance plan set to back up and then remove .bak and ..trn files after 4 days. It removes .bak fine, but not the .trn for some reason. This is ok for a while, but adventually it fills up the drive. I tried deleting and recreating the maint plan, but that didn't help. An...more >>

Hi Folks, I am using an xp_cmdshell sp to start a C# app. This app downloads data files and imports them into DB. It has a logging functionality built-in and when executed from the command line or WE, it records it's progress into a log TXT file. But when executed via xp_cmdshell, it doesn't ...more >>

Hi, I'm doing a proyect with Visual Basic where I have to use a database that I've already made on SQL SERVER. In my computer I had to install WINDOWS 2000 family server, to be able to install SQL SERVER as server not as a client, I can see perfectly and totally the database from VB. The prob...more >>

There appears to be a limit of ~16378 user Id's possible per database. When adding users we eventually get the message: > exec sp_adduser 'testUser', 'testUser', 'user_group' Server: Msg 15065, Level 16, State 1, Procedure sp_grantdbaccess, Line 160 All user IDs have been assigned. All of ...more >>

I'm having problems using the DB2 Connect ODBC Driver in my SQL Server 2000 DTS packages. It started occuring after installing MDAC 2.7 (which is necessary for the .NET framework). This error occurs when I try to open the transformation that uses an ODBC Connection object that is pointing to t...more >>

I Need help with accessing an SQL server from a web method. Trying to connect from localhost to ms sql server 2000 sp1 and get the error: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied. at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTran...more >>

Hello all, I have a scripts which needs the information about the database in which it is running. How can I find out this information. For example if I want to know the User who has satrted this report, I use the SYSTEM_USER procedure. Does an equal procedure exist to find out, on wh...more >>

Hi I am trying to design an IO subsystem for my SQL Server and for that I need to try and predict IO activity on each table in my MSSQL Database. My idea is to move the hottest tables into special disk subsystem away from the less hotter tables. So far I have gathered that we have three tabl...more >>

Hi, I'm a newbie with SQL Server 2000 Developer. I know that it is supposed to install on Windows XP Pro, but I keep running into the following message: "The logon account cannot be validated for the SQL Server service. Validate ...". I've looked for help and taken the advise of trying t...more >>

To anyone that is able to help.... What I am trying to do is this. I have two tables (Orders, and OrderDetails), and my question is on the order details. I would like to set up a stored procedure that essentially inserts in the orders table the mail order, and then insert multiple orderdetails...more >>

Hi, Quick question, I have about 20 users in my local server and database. We are looking to restore that database to a new server in a new network and still be able to retain the database users. We have created all 20 logins in the new server (exact names) but when we restore the database ...more >>

I have the following table with indexes CREATE TABLE dbo.Scratch ( ItemID int IDENTITY (1, 1) NOT NULL , Login varchar (12) NOT NULL , StartDate datetime NULL , PayDate datetime NULL , LastDisconnect datetime NULL ) GO CREATE INDEX idxPayDate ON dbo.Scratch(PayDate) GO ...more >>

I have an application which runs in many different stores. Stores that are within a chain want to be able to share their client lists. I want to implement a sync process where each application would ping a central server every 5-10 mins to check for changes made by any other store to the clien...more >>

--Table Create table tableName (Field1 int, Field2 int) Table Data Field1 Field2 1 10 2 10 3 10 1 20 2 20 3 30 I need Like a Sql statement like 1 10 (any separato...more >>

I am working with SQL 8.00. I have the following tables TABLE ClientInfo CheckNum Account Name Addr1 City State Zip --------------------------------------------------------------------- 12345 11111 John 123 Mary St Miami FL 33139 54321 22222 Mary ...more >>

Hi, How can i read information in Transaction Log in SQL Server 2000? Thanks...more >>

Can we make a Index on 2 or more tables Table a: Col1 int col2 varchar Table b: Col1 int col2 varchar can you have a single index for two tables a and b on the column Col1. Is this possible in SQL-Server. As far as i know you can make an index only on one Table....more >>

In an SQL statement which concatenates several fields I get a null value returned if any one of the fields are null. Is this to be expected? For example : SELECT tblMember.memberAddress + ' ' + tblMember.memberTown + ' ' + tblMember.memberCounty + ' ' + tblMember.memberPostCode + '<br> '...more >>

This is probably a common problem with a standard design pattern, but I'm having trouble finding the solution. I have a table with a lot of columns, for this example I'll just use three but in reality its more like 20. Create Table myTable (int col_one primary key, int col_two, varchar(20) ...more >>

When I add a unique key constraint to column in SQL 6.5 why does it also create an index. e.g. In the table subaccounts I added a unique key constraint for the column login and SQL creates an index with the name UQ_SubAccounts_2__19 (UKC). Does this also mean that there is no need to create a...more >>

I have converted access 97 to access xp project and connect the table from my sql server 2000. my problem is that when i run my report it says "Invalid SQL Statement. Check the server filter on the form recordsource" now this is my recordsource in my report. SELECT Students.IDNo, [LastName]...more >>

I tried to use DTS for copying a sheet from Excel to SQL. For some reason, the last column shows up error saying source's column 5 buffer is too big.. and failed it.. is there anything that I need to watch out?...more >>

Hi, guys, I got a problem when trying to paging the recordset. the problem is even I set the pagesize but the first page will always show all the records and the number of records that shown on the second page will always all records number minus pagesize. for example, if I have total 5 recor...more >>

I have several stored procedures that run fine from my SQL Server database (via the exec command.), though when I call these procedures from my web application, they do not complete. I have other procedures that in fact do run fine through my web application though, so I do not believe its a fr...more >>

Hi again Sorry - I didn't mention that I want to use a SQL Server DB on a remote server -that is included in a Web Hosting package I also have MS Office Pro 2000, Visual Studio 97 and Virual Basic 6 - do any of these provide useful tools for managing a remote SQL Database? Thanks again i...more >>

Hi I am a web / database developer who wants to learn / practice developing websites using Active Server Pages and MS SQL 7 or MS SQL 2000 Can anybody tell me if there is an inexpensive way to do this? I already have experience using Active Server Pages, ADO and MS Access. Available ...more >>

Im trying to make this into one select statement witout using variables.. declare @sum1 int declare @sum2 int declare @sum3 int select @sum1 = sum(CLIENT_PLANNED_SERVICE.TotalUnits)from client_planned_service select @sum2 = sum(RECORDED_SERVICE.UNITS) from recorded_service where (R...more >>

Hi everyone I have the folowing situations with my backups. With Database Maintenance Plan is created backup strategy. Full Backup is performing every day at 1.00 am. And Romove files older than: is checked on 2 days, and it's working fine. I am interested how can i perform this action with t...more >>

Usually when I create an index in the Query Analyzer, no message is returned other than the command completed. But sometimes I get a message than the index is being rebuilt. I have come to ignore this message as it appears to not be an issue that needs research. I am now doing more and more in...more >>

Hi all, I have a query that scans huge table consists of 8 or more millions records. The funny thing is that if I use the query with local variable, the query takes more than 1 minutes, whereas if I hard code the value into the query, it takes about 1 second. Here are the queries: WITH ...more >>

After installing MS SQL Server 7 several databases appears in the database folder named: distribution, master, model, msdb, northwind, pubs and tempdb. Is it safe to delete any of those pre-installed bases? Regards Kim Christensen ...more >>

I have a stored procedure named mySP that looks basically like this: Select Field1, Field2 From tblMyTable Where Field 3 = 'xyz' What I do is to populate an Access form: DoCmd.Openform "frmMyFormName" Forms!myFormName.RecordSource = "mySP" What I want to do in VBA is to open frmContinuous...more >>

I am trying to upgrade the SQL Server 6.5 on one server to SQL Server2000 on the other machine. At that time, i am getting the following error: "The local SQL Server is running under localsystem priviledges, preventing the upgrade wizard to connect to the export server." I am logged on th...more >>

Hello all, I've written a stored procedure which runs OK for the first few thousand records it processes, then around about the 10,000th record it suffers a sudden and dramatic drop in performance (from about 40 records per second to about 1 per second). I've found that when this happens, ...more >>

Where can I download "Enterprise Manager" I'm new to SQL....more >>

I am using SQL Server 2000 This doesn't work :- DECLARE @topnum AS INT SET @topnum = 2 SELECT TOP @topnum * FROM tblMyTable I also tried :- DECLARE @topnum AS INT SET @topnum = 2 SELECT TOP (SELECT @topnum) * FROM tblMyTable but this does :- SELECT TOP 2 * FROM tblMyTabl...more >>

Hi, One of our customers claims that the sp_helpdb truncates database name larger than 24 characters, the release is 7.00.1094, did any one encounter such a problem ???...more >>

hi there, I have to fetch values from a database and store them in a vector in my c++ application. What is faster? if i fetch the values sorted from the database with a 'order by' clause in the sql-statement or if i fetch them into the vector unsorted and sort the vector then? Thanx fo...more >>

Hi, I am facing one more problem. As soon as i started the sql server first time, my windows 2000 machine reboots and i was not able to start sql server again. I had to uninstall and install again and this process repeats. Do anyone has idea about what is happening ?? Thanks a lot, Dines...more >>

Hello I would like to get the content of a field based in the field Name. Suppose a table with a field Named 'LastName' for wich there is a trigger after update I store the field name in a local variable Set @ColName = 'LastName' How can I retrieve the value of the @ColName from the in...more >>

Hi All, I want to use MS sql server edition 2000. I have installed the server. Now i want to create the database on this server with tables and triggers. Can please someone suggest me how to do this ? Thanks a lot, Dinesh ...more >>

Hello, We are using Access ADP as a front end, SQL Server 2000 as a back end. We have a customer contact database. We would like to limit certain users to only receive certain records based on Windows NT group membership. For example, Eastern Sales Group can see clients located in their re...more >>

I have a customer who has Access databases at local sites who wants the data replicated nightly to MS-SQL Server using broadband connection. I have very limited knowledge of best practices to accomplish this reliably. Right now they are using some Java app that exports the Access data then em...more >>

Here is the block of the sproc that I?ve created. All lines execute fine except for the ?exec master..xp_cmdshell @reboottc?. In order to make this work from the query analyzer I had to set the MSSQLSERVER service to have the same credentials as the thin clients we are trying to shut down, tha...more >>

Hello, I'm upgrading from SQL 7 to SQL 2000 on another box. To minimize the downtime I would like to 1) backup my sql 7 database, 2) copy it to the new box with SQL 2000 already installed, 3) restore the database on the SQL 2000 box, 4) Shutdown my sql 7 database, 5) Copy the transaction ...more >>

greetings!! the below trigger works fine in SQL 2000 and doesn't take up much resources, it's a very simple solution to trim text fields at input in SQL. I know a lot of folks will say to create input masks at the UI level and that SQL is a restricted back-end DB, but to be quite honest I don't ...more >>

I have to create a stored procedure to purge "x" # of records from a table. I have two tables (script below): Schedule ScheduleHistory I need to purge records out of ScheduleHistory. The problem is that the # of records that needs to be "kept" is dynamic, and stored in the Schedule table...more >>

Hi when i try to run a query using linked servers, i get the following error. Server: Msg 125, Level 15, State 1, Line 1 Case expressions may only be nested to level 10. I do have more than 10 case statements, it works fine when it is less than 10. can anyone tell me if there is a way to h...more >>

As of about 2 months ago, sql queries on a frontpage .asp page required that the variable from the sending page be included in the query results, or the query failed. The query also seemed to become case sensitive. i.e. "select last,first,sales from names where city = '::city::'" will fail u...more >>

I have 2 table: 1->Deals with ID,IDCompany,Concluded 2->DealDetail with ID,IDDeal,DateStart,DateEnd I want to extract the list of Deals with the DateStart less than Today, not concluded. the list must have this fiels: IDCompany, DateStart,DateEnd I've did this query: ------------ SELECT ...more >>

Coming from Oracle background: How do you carry thiese admin tasks in SQL 2000 Environment : Check instance availability Check listener availability Check alert log files for error messages Clean up old log files before log destination gets filled Analyze tables and indexes for bette...more >>

Is it possible to use the REPLACE function in SQL Server 2000 so that it returns a string containing only alpha-numeric characters (much like using regular expressions)? Thank you in advance for any suggestion. Darren....more >>

Thanks in advance for you help. SQL Server 2000 I have a complex join, but a simple example will do the trick so... table1 (placement records, possibly many per case,highest ID being most recent) ---------- placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Number table2 (Organ...more >>

We have a win2003 server box that is using MS-SQL 2000. We have a 20 user license on this server at the present time. We have a need to allow up to 5 simultaneous additional users access to a subset of this data from our IIS server. How does this affect my current SQL Server License? We can r...more >>

Here's my update statement: UPDATE Item1 SET reviewloop = 1, currentreviewstate=5 WHERE itemid in (SELECT itemid FROM Item2) The thing is: the table Item2 DOES NOT HAVE a field called itemid. So, I should receive an error, right? Not so.Instead, every single record in Item1 was updated...more >>

Hi, The 'properties' option of a table through the 'Enterprise Manager' shows different no of records against the row selection through sql query analyzer. Any answers why so? Thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get r...more >>

I am having a problem creating a many-to-many-to-many type relationship. It works fine, but when I create a view to query it and test it, it does not generate the results I expected. Below if the DDL for the tables and the SQL for the view. Any help would be most appreciated. ...more >>

Hello! You can do it in Oracle, but how to do that in MS SQL. I don't whish to write select * from a where field1fromA=1 and field2fromA=2 because i want to generate (1,1) from a subselect... Thanks Bjoern ...more >>

Hi, How to copy database diagram (all data) to other server using only MSDE server and VS.Net Proffesional? I have no Enterprice manager. I have to move database (create on my machine) to my customer server. Is some possibilities? -- *Pozdrawiam,* Klaudiusz Bryja ...more >>

We have an old database & server that is supposed to be used for reference only. Access to this is minimal and read-only. What I need to do is find out what a specific user did on a certain day. I know which user it was and I know the day, but I need to prove if they did anything on the server...more >>

Hi: I have two diferents hosts with sql server 2000: server1, server2 with two diferents bbdd database1, database2 with two diferents schemas owner1,owner2 with the same set of tables table1,table2,table3,... these tables have fk a pk with identity columns I need pass only all rows from ser...more >>