I have a database that tracks players for children's sports clubs. I have included representative DDL for this database at the end of this post. A single instance of this database supports multiple clubs. I would like to add support for letting each club define and store custom information abou...more >>

I'm looking for SQL code to do the following. TableA GrpID,IndID,Locked 50001,10001,0 50001,10002,0 50002,10003,0 50002,10004,1 50002,10005,0 50003,10006,0 50003,10007,0 50003,10008,0 50004,10009,0 50004,10010,0 50004,10011,1 50004,10012,1 I would like to return GrpID's ...more >>

I need to compare and update a Binary(8) coln with values from an Int(4) coln. Could someone please explain how to do this..?? thanks Sunit ...more >>

Hello, I want to access my database server from the internet. I've created a rule on my router to redirect port 1433 to the desired machine, but still get a Server does not exist, or access denied message. When I change the (external) ip to the local IP address, connection succeeds. Is ther...more >>

I've almost completed a small project with MS SQL 2000 server in the BE and Access 2003 in the FE. 5 concurrent users, 30 tables, 35 forms and subforms, 13 reports. Not so happy in the beginning as soon as Access Event Model was shown (Crappy at minimum, even compared with not-anymore-existan...more >>

I need to update the columns to blanks if it has Nulls; Most of the time we do not have Nulls and to check that in a 3.2 million table it takes 2 minutes and 45 seconds. (To do select count(*) from <table> where <columnname> is Null) . I just cut and pasted a part from my stored procedure: ...more >>

Dear All, We have a procedure that takes12 minutes to run on the first server but that same procedure now takes 3 hours to run on the second server using the same data. Does anyone have any suggestions why this is happening and how to make the procedure faster on the second server Than...more >>

Hi, I have a table with two columns Task and Employee. It lists all the tasks and the assigned employees. The same task will have multiple roles with an employeename or 'manager' as the data. If I have the following data 'sales', 'john' 'sales', 'manager' 'dev', 'manager' 'make_coffee...more >>

I have a TSQL script to add daily tables to replication and then run the snapshot agent to distribute them to two subscribers. The script executes without errors, but when I check the running jobs for each server I see the following: JUST AN EXAMPLE Job1 'Category' REPL-Snapshot Job2 'Categ...more >>

Is there a stored procedure or query that will count all the records in all my tables in a database? Many thanks Sam...more >>

I've got MSDE, SQL Server 7.0, installed. I want to handle an .mdf developed by someone else so I loaded the .mdf into my /DATA folder. But, MSDE/ODBC can't see the database. I subsequently discovered that it was created using SQL Server 2000. Is this the reason that my MSDE 7.0 doesn't see the ...more >>

I developed a console application that will continually check a message queue to watch for any incoming data that needs to be inserted into MS SQL database. What would be a low-cost method I could use inside this console application to make sure the MS SQL database is operational before I per...more >>

Hi everyone, I'm learning T-SQL and I have created a copy of the pubs database called pubs2. I both the original pubs database and pubs2 I have created an identical view called TableFields. In the database in which it resides this view retrieves: Every table in the database as WS_TABLE, ...more >>

My client has a custom app using a Sybase Adaptive Server Anywhere 7 DB backend. I need to regularly extract read-only data from a couple of tables in this database and I would have thought it would be simple to setup a System DSN for this purpose, however I am finding it difficult to locate an ...more >>

I'm new to adp w/ sql server but I have to use it on a project i'm doing... One of the MUSTS for this project is the ability to update a 00 - 09 text value with the appropriate text description from another table... Easy as pie in .mdb. Of course In the stored procedure it barks at me and tells...more >>

Code page translations are not supported for the text data type. From: 1252 To: 950. I would like to know what this message means. I also installed the language packs in advanced settings, Everything is set to English. My windows XP computer is XP English. For some reason I cant get an update ...more >>

Hi all, I just got SQL Server 2000, but I already have MSDE installed. Is there any reason I would want to keep MSDE around on my computer if I install SQL Server 2000? I use MS Visual Studio 2002 on Windows XP SP2, in case it makes any difference. I intend to deinstall MSDE before insta...more >>

Hi. We need to create a view of our active directory users (we have 2500). I found out that there is max page size of 1000, so we cannot get more data. Anyone found a solution to that problem? Thanks ...more >>

Anyone have any tips for using Java against SQL Server? Our IT director is thinking of making us a Java shop, but I really would like to stay with SQL Server as our RDBMS. We have Oracle Financials but our in-house development (currently VB6 client-server, soon to be replaced by a service-orie...more >>

Hi all, I set up our Oracle Financials as a linked server to one of my SQL Server boxes. On running a test query, I got the following error message: OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. OLE DB error trace ...more >>

Can anyone offer any advice. I was wondering whether MS SQL server offered any facilities to trigger an event after a period of time had lapsed? e.g. once a week check data and if it is out of date email an related address. many thanks ...more >>

Hello, I need to be able to replace only the first occurance of a space character in a column. Reason being is the data in the column I am trying to replace seems to have umpteen space characters after each entry, so a simple replace function will replace all the spaces after it with what I...more >>

Hi, I have trigger that enforces the creation of a sortorder that is always 1 digit higher than the current highest on Inserts. This trigger works great if I add one row at a time so I think the logic is sound. However, I have a Stored Procedure that copies a bunch of rows into this table ...more >>

Background: I'm new at SQL Server (no experience, zip, nada) but getting pretty good with developing applications in Access(VBA) and in VB using ADO.... Currently I use access to and GIS software to build data management systems. I have become pretty proficient in programming these systems in...more >>

Hi We have the following setup W2k server with IIS 5.0 Asus P4P800E Deluxe Mobo P4 2,8 Mhz - 1 Gb Ram 3 sata harddisks 1 hd = 2 partitions c: boot/system f: backup 2 hd's in a raid 1 (mirror) Promise controller e: webroot Where would be the best partition to install a MS SQL ...more >>

Hi all, I have the below user-defined function on mssql 2000 and I can't work out why i'm getting the following error: ----- Server: Msg 156, Level 15, State 1, Procedure fnCalculateOutworkerPaymentForBox, Line 15 Incorrect syntax near the keyword 'IF'. Server: Msg 170, Level 15, State 1, P...more >>

When I login using QA to my SQL Server database, it takes 15-20 seconds to establish a connection and open a query window. Drilling into a database via Enterprise Manager is similar. Once the connection is established, the server runs plenty fast however. Can someone tell me why it could tak...more >>

Hello, I am doing the web based project for the final year of my couse in Information Technology using Asp.net and ms-sql server.But to start with that I need to connect my web pages to the database. So Can anyone tell me how to connect my page web to the ms-sql server using Asp.n...more >>

I need some tips to boost the performance on the following query. The problem is that it times out once in a while, and then again runs normally in most cases. The clue is to compare a textual value stored as an image data type, but I don't know if the cast operation could be done in another w...more >>

What up-to-date books on this topic can people here recommend? Thanks. - Bob ...more >>

I have a pivot chart that displays 3 data series. Series 1 values range from 17 to 106. Series 2 values range from 1 to 18. Series 3 values range from 0 to 1. When I display all three series on the chart, the value axis labels run from 1 to 120 with major unit lines at intervals of 20. W...more >>

Greetings: I have a SQL 2000 database, in which about 1% of the records are in lower case. I need to make them UPPER CASE. Is there a function to determine and change the case of existing records, or will I have to re-write the records manually? Thanks, DW ...more >>

Hi, On first sqlserver install, what's the default base to test connect with JDBC please ? Is it "system" base with sa/master password ? Thanks, -- Jean-Luc M. ...more >>

Hello, I am writing a stored procedure that will take data from several different tables and will combine the data into a single table for our data warehouse. It is mostly pretty straightforward stuff, but there is one issue that I am not sure how to handle. The resulting table has a column...more >>

I'm a quantitative securities analyst working with Compustat data (company fiscal reports and pricing feeds). My coworker came across a problem that we fixed, but I'd like to understand 'why' it was happening and just don't get it yet. Here's the starting query (reduced to simple prefixes): ...more >>

Hi All, Would someone give me an explanation on how SQL 2000 server backs up a user database? Is SQL server make a copy of user database first, and then backup it up to a defined backup device? Or is it going straight to the defined backup device. Thanks, Bob. ...more >>

This is a fairly simple question, but what is the easiest way to: create a new numbered column (where value is simply the row number) in an existing table and setting it as a primary key? ...more >>

I have a custom application that on occasion requires thousands of TSQL files (on the file system) to be compiled to the database. What is the quickest way to accomplish this? We currently have a small vbs script that gets a list of all the files, the loops around a call to "osql". each cal...more >>

I read somewhere that views (and queries) execute on client machines while stored procedures execute on the server. Is this correct? ...more >>

Getting a bizarre reading from sp_spaceused... Just done a shrink, and am getting the following: dbame:MyDataBaseName db_size:17462.00 MB unallocated space:-10656.61 MB Any ideas? Sql 2k sp3a, win2003....more >>

Please let me know the general reason and solution, if any. For following problem. Environment: Stored Procedure(SP) A on Database(DB) AA on SQL-Server AAA. SP B on DB BB on SQL-Server BBB. SP A on DB AA submit SP B on DB BB (Database Linkage by Link Server). After finish processing SP B, p...more >>

Hi, I have a stored Procedure im MS SQL 2000 and It runs normally. After alter the table, it suddenly slow down and I have tried to run sp_updatestats and there are no improvement on it. It runs on Win2000/SQL2000 with Dual Xeon 3GHz with Hyper-threating. I have restored the database on oth...more >>

We have a bookings table that records various information and for each booking we record various events that users have performed on that booking along with when the event occurred. For example:- Bookings EventLog --------- --------- BookID PK ...more >>

I'm trying to translate the following calculated column, but am having no luck: IIf([License]![RecordID] In (select [CurrentLic].[CurrentLicRec] from [CurrentLic]),1,0) AS iscurrent It's supposed to return a 1 to the iscurrent column if the recordID is in the list of current IDs, or return ...more >>

We have an employee table that contains bank details and are experiencing problems with account numbers being erased and lost. In order to track down why this is happening (either due to our application code or SQL replication) we'd like to be able to prevent certain columns from being delet...more >>

I created table with "id" field as "uniqueidentifier" (primary key). I connect via ODBC from MS-Acces. -->>> Don't know how to something like "Autonumber" in MS-Access. Let me know If any trigger or something like this is required. Please post some code of trigger if need. Until now I have...more >>

Hello, The population of my analysis is customers that have product1. And I am trying to get the % of them that also have product2, the % of them that also have product3, etc. Now I am doing it one by one, i.e., when I do the % of product2, I make all other groups comments (please see query b...more >>

Hi i have a problem with IIS6 and MSSQL and i hope that someone can help me. this is the configuration: Windows 2003 IIS6 MSSQL 7.0 Standard Edition ADO 2.5 I have the following error that occur randomly when browsing my websites. Microsoft OLE DB Provider for SQL Server err...more >>

I'm currently implementing a German ERP system and I'm having a terrible time writing reports/SQL statements. The database is MS SQL Server 2000. I want to set permanant aliases for the columns that I can use in SQL statements. Is this even possible? All of the research I've done implies tha...more >>

I need to create a view that scores a research assessment. So I have questions 1 through 10, but the problem is that Q3 and Q5-10 have to be recoded so that if the response is 3, it becomes 0, 2=1, 1=2 and 0=3. So this is what I have. I keep getting an error message that there is "incorrect sy...more >>

Hello! I have a database in SQLServer 2000 on a server named aludb. In the SQLServer data directory I have these 2 files: aludb_Data.MDF (4Gb) and aludb_Log.LDF (16Gb). I need to free the disk space on the server and I would delete all the logs... How I can do this? I tried to backup only the...more >>

How to enable remote connections on TCP/IP? I have just installed for first time SqlServer 2000, and cannot connect from other host via ODBC. I give hostname or address but it does not connect. I checked on my firewall that there is no listenning socket on port 1433 nor 1440 on sqlserver host...more >>

Hi, I am trying to concatenate 2 text fields to update a sql db column by using: UPDATE Messages SET private_messages = private_messages || @newmessage But the stored procedure I am using doesn't accept ||, I've also tried CONCAT() and CAT() but so far this has failed. Is there another w...more >>

Hi I'm trying to update a db based on the select statement which has ORDER BY in it. And due to that I'm getting error which states that Server: Msg 1033, Level 15, State 1, Line 13 The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also s...more >>

We have a production server and a bunch of development servers. The Production server is located in a NOC (Network Operations Center) facility and is physically unaccessible on a regular basis. There are DTS packages on the production server which we'd like to pull down to our Dev servers. ...more >>

I've got a DTS package that runs an active-x script. The script is simple - it runs a stored procedure and saves the results to a CSV file. I kept getting this error message when trying to run it saying that the recordset object I was using could not be used when closed. Well, it didn't make a...more >>

I have two servers running SQL Server on the same network and I am receiving errors registering the various instances as follows: SERVER1 tried to register the local instance (by netname, ip address and local) ERROR Msg: SQL Server does not exist or access denied (ConnectionOpen | Connect() ...more >>

What is the best data type to use within SQL server in a field for storing currency? Regards, Ciar=E1n ...more >>

Has anyone any idea what could be causing this problem. I've a large txt file, with each item on each row seperated with quaotaion marks and seperated from each other by a comma. I can import it no problem to Access, but when I try to import it to SQL server, SQL Server gives me that "cannot fin...more >>

Hello, For analyses and reporting I would like to add a 'ranking' to a table/view. Example: Using the 'order by desc' clause in query I get a list of Customers ordered by Turnover (descending). I would like to add that ranking numbers (same as recordnumbers) in the query. I would like to hav...more >>

Hola a todos, Tengo un problema, no se me ocurre como solucionarlo. Utilizo SQL Server 2000. Tengo una tabla (tblRespu) en la que quiero insertar tres valores: el primero procede de una variable @CodPregunta, el segundo tiene guardada en texto la información de la columna de otra tabla (tb...more >>

Having recently had excellent service here (many thanks, Erland!), here's another wee problem. SQL 2000 I need to get a report which will display the movement of people thus: ID DIRECTION NAME DATE VEHICLE REGISTRATION The value of DIRECTION can be either "Inbound" or "Outbound" ...more >>

Greetings, I'm looking for a little guidance. I work for a medium sized bank with about 20 SQL servers in 2 locations. We have two backup schemes - Less critical SQL boxes have nightly DB backups and Transaction Log dumps, more critical boxes have additional hourly transaction dumps. The...more >>

I am trying to know how to read many XML files within one data flow task. I have many many files that I would like to apply to with the same data flow so I need to know how to read them in and what task should I use under SQL 2005 DTS/Integration services. Thanks for your help! JON ...more >>

I attempt to back up a database on one server and restore it on my local machine. This is what the query text looks like: restore database model from Disk='c:\JQJ\mydump\model.bak' with replace I get this error: The backup of the system database on device c:\JQJ\mydump\model.bak cannot...more >>

I have encountred situations like this before, but this one has me stumped. I have a pretty simple SP that collects information about residential properties from a large database. First step is to query on the basis of address or location, and collect a temp table of property IDs. Second st...more >>

I have a field of type Real in my SQL 2K database. I stored a value of ..35 in the field. When I "Open Table"->"Return All Rows" in the Enterprise Manager I get back .35 for the field value. I went to The SQL Query Analyzer and executed the following T-SQL: SELECT field FROM table ...more >>

Hello, I am trying to count the number of customers for each product group. One customer may have multiple products. Now I am doing it one by one, i.e., when I do product_id like '111%', I make all other 3 groups comments (please see query below). Is there a way that I can get the #s of the 4...more >>

Hi All, I'm not sure how best to describe what I'm really looking to do, so I'll just give you my example and hopefully someone can help. I have a table that looks similar to this: Primary Key Field Type Field Value -------------------------------------- 1234 FName ...more >>

Hi all. If I've got a query which has a field with seconds in it... how will I use the Convert function to get my field converted to the format: HH:MM:SS ? The field with the seconds in is called: "Diff" Thanks alot Rudi ...more >>

I'm doing some performance reviews and wish to know what tables SQL has pinned in memory and which ones have are loaded through usage ... Is there a way ? Thanks, Craig ...more >>

Hi, We are using SQL Server 2000 on Windows 2000 Server with IIS installed. Can IIS be uninstalled without affecting SQL Server? Thank you! ...more >>

I have many stored procs in my database and I can call them just by their name uspMyProc with success always. However, I just created a user function ufnMyFunction as the same user that I created my procs but when I call ufnMyFunction it fails unless I preface it with dbo. . How come the stored...more >>

Hi, I am having trouble importing data from an excel spreadsheet into MS SQL Server 2000 using DTS Wizard. The DTS import process is successfull, no errors, but only 50 rows of approx. 1500 rows of data are imported. I tried to remove 20 rows in the excel spreadsheet in the interval row 0-50....more >>

Hi, I have a table t1 with columns a,b,c,d,e,f,g,h,i,j,k,l I have created a clustered index on a,b,d,e which forms the primary key. I have created a covering index on all the columns of t1. There are 1 million rows in this table. My query chooses the TOP20 rows based on some filter con...more >>

Hi, If I run an insert statement from the query analyzer and then try to open the table from enterprise manager then it takes long time to open the table. But this problem dissapears when i put the statement inside Begin/End Transaction statement. Any idea why this is happening? Thank in...more >>

I have a table in my databse, and there is only one field in the table and there are 3 rows which are similar and i want to delete the first row of the 3 row . How cani do it ??????????????????? . Please Help me out .........more >>

Is there any way of storing an image file into a specific Table . It would be of great help for me if i come to know something about it....more >>

Hi, Currently it is running on SQL 7.00.623 on Winnt 4.0+SP5. Now i want to upgrade this with SP 4. As i follow the installation procedure according to Microsoft SQL7SP4 readme help, It hangs during the installation of Mdac_typ.exe. Don't know where and whats went wrong with the p...more >>

Hello, I am doing a lot of work with stored procedures at work now and am wondering if there is a way that I can step through the code line by line and set breakpoints on it like I do in VB/VBA to test variables/parameters. Regards, Jayne...more >>

In my .NET code, with SQL Server 2000 as the backend, I need to create an exact copy, without data, of an existing table in the same database, that the user selects from a combo box. Any ideas on how to do this? ...more >>

Hello, I have MS SQL Server running on a server computer. I am trying to connect to it via ASP from a .asp file created in a virtual directory. I am using IIS as the web server. I am connecting from a Windows 2000 computer. When I go to Start -> Settings -> Control Panel -> Administr...more >>

hi!!! the following is the sql which is veri slow cos of the 'Not In' clause, would appreciate if u anyone can suggest any other way to bring about the same result SELECT Id, LOC, AGENCY, BATCH FROM tblRows WHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCY ...more >>

Hi, I have a SQL Server for a SAP database with the collation SQL_Latin1_General_CP850_BIN. When I connect to that server (or, in this example, to another server with the SQL_Latin1_General_CP850_BIN collation) and execute a select, the accents seem weird: select t.TEXT from [GC-SAP02].P01....more >>

Hi I wonder if anyone can help with the following: when using an autonumber in access when you use .addnew you automatically get the field in an autonumber field i.e. rs.addnew jno=autonofld rs.update jno will have a value. however in SQL you have to update first and then find the re...more >>

Hello! My client has a need to be able to store Japanese characters in their PeopleSoft database. So we need to change the character set from from Latin1_General (1252) to Japanese character set (932) on SQL Server 2000 Enterprise. I have 2 questions: 1) I would like to know if in SQL Server,...more >>

Is there a way to construct a query to select for whole words only? select id from bookdata where titles like '%Test%' gets everything with test somewhere in the field. So you could get records which have test as a subset of a larger word like 'testing' etc... What I am interested in is fi...more >>

My vb application is displaying an error message when it loses sql connection. My client thinks it has nothing to do with their sql2000 server and tells me they never 'stop and start' sql services. But I think they do. Is there a way to find out the status of sql connection from the log? Or ...more >>

Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into a table (c) re-queries another table using a subquery which references the inserted table (correl...more >>

Hi Every1, This is the first time I'm trying to use columns by themselves instead of using individual values. And I'm getting an error. Here is my sql INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT) SELECT (SETI...more >>

SQL Server 6.5 My clients are troubled by a growing Transaction log, but they don't want a lot of expensive maintenance as the contract is about to expire. The database used to be marked for replication, but it seems almost impossible to stop records being written to the Transaction log even ...more >>

I'd like to do this: Have a SQL Server database hosted on a public facing web server. I want it to be held at an ISP/Web hosting company. Though I have ADSL here I don't want to manage a server. I want some ASP pages accessing the database. I would also like for a very small number (pr...more >>

Hello All, DDL Statements: CREATE TABLE [dbo].[Table1] ( [MyDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO I have a varchar column which represents dates in YYYYMMDD and MM/DD/YY formats. If I query using: SELECT MyDate FROM Table1 WHERE DOB LIKE...more >>