Hi, I'm fairly familiar with database normalisation rules but I can't really figure out what I should do with the specific design I'm dealing with. My users will need to enter data for several facilities each year. So I have a main table: tblFacilityReportingYear -----------------------...more >>

I'm having problems with some charactors in SQL statements. For example, the following would give me an error. UPDATE Table SET Column1 = 'This statement's not too long' WHERE ID = '2' I can see that the error's due to the ' in statement's. I'm assuiming I need to escape this somehow. How ...more >>

Hi all, I've installed RAC4SQL2K all the way up to the 2.2.1 update. The first crosstab I produced using RAC4SQL2K works perfectly. It's called from the following stored procedure: ---------- CREATE PROCEDURE [dbo].[spPageAccess] AS Execute rac @transform = 'COUNT([PageAccess].[PageAcc...more >>

we have an older version of MS SQL (7?) server running on a LAN..pretty much a RedHat network and our admin refuses to help with MS problems (don't blame him) question: a) can a remote computer user (running MS Access in Windows)view the DB? b) if so what do i have to do to the SQL server ...more >>

Help! I've been hacked! I'm running SQL 7 Desktop (MSDE) and started getting these messages at startup that servudaemon.exe was going to be shut down. Then, at one point, I keep getting prompted by Norton Firewall that serve-u.exe is trying to access the Internet. It suggested I deny it, which I...more >>

Can anyone point me towards information about bitmap indexing in SQL Server 2000. (before you ask, this has nothing to do with image files or fields, it is a type of indexing used for low cardinality field indexing). I am working on a Data Warehouse project and this came up as an issue, and I ...more >>

I'm currently using the text data type with a length of 16. I was wondering if there was a more efficient data type or length for storing web pages? I chose this simply because there is essentially no limitation on the page size, however, I am confident that no web pages will be 2Gb so I'm lookin...more >>

I created a database with a log file o 100mg. All of a sudden i find the size decreasing to 9mg!!!! My understanding is that you can increase the size but never decrease it. So, does the SQL engine decrease the file size on its on!? I backup the transaction log once daily. Please help. T...more >>

I have an UPDATE statement that uses the Transact SQL extension of the UPDATE-statement. I want to replace this with an equivalent ANSI standard query, but I fail to see how to do this. Here's the query: UPDATE rx SET IsGxwApplic = CASE WHEN (S...more >>

I have 2 identical databases (one test and one to be live soon) in different installations. Both have an identical table with approximately 4.5 million rows and a row width of 339 characters. Both tables have the same primary key constraint and clustered and non-clustered indexes. Both tables...more >>

Hi, I would appreciate any help with a query to do the following Assume a table with two columns Person and Interests Person Interest A Hockey A Tennis A Rugby B Hockey B Rugy C Mountain clibing .... HOW do I select all perso...more >>

Hi, I have an existing table with over 8,000 rows that I need to add a leading zero to a column of data. (i.e. current no: 2000, with leading zero: 02000). This data lives on in MS SQL 7.0 on a Windows 2000 server. Thanks Chad...more >>

Hi All, I have a table this : CREATE TABLE Tbl ( OrderId INT, Product CHAR(1), Price INT ) INSERT INTO Tbl VALUES(1,'A', 2) INSERT INTO Tbl VALUES(1,'B', 4) INSERT INTO Tbl VALUES(1,'D', 2) INSERT INTO Tbl VALUES(2,'A', 1) INSERT INTO Tbl VALUES(2,'B', 1) INSERT INTO Tbl VALUES(...more >>

Hello, Is there anything weird about upgrading a SQL 7.0 Standard Edition server to SQL 2000 Enterprise Edition? Is it the same process as upgrading SQL 7.0 Standard to SQL 2000 Standard (which I've done on a couple of other servers). We want EE in order to bump up the memory beyond 2Gb. S...more >>

Hi everybody I need query that will determine which tables are created on primary file group or secondary file group... I know that i can exectute sp_help 'tablename' to see on which file group the only one table is. But i have 2000 tables and it is very slow to execute sp_help for every tab...more >>

This should probably be really easy but I cannot find the right combination of SQL statements (pref. SQL-92 compliant) to return me a list of databases from a SQL Server installation with a column that signifies whether the database is a system database or not. Can anyone help me on this? Any...more >>

Hi all, after installing MSDE 2000 on Win 2000 Server I have an error message in aplications event log : SuperSocket info (SpnRegister) :Error 1355. Who knows what it mean ? TIA, Marek ...more >>

Does anyone know where I can purchase and download a copy of SQL 2k Dev edition? I'm running Win XP on a laptop and need this so I don't have to be connected to the network to test stuff. I appreciate any assistance you can provide! Rob ...more >>

I have an Access query that starts 'SELECT Iif(FieldA > 20, 'Yes', 'No') as BoolExp.....' that I want to perform in SQL. However, 'Iif' serves a different purpose in SQL (some kind of crosstab thing, it seems). Does SQL have an equivalent for what I use in Access? Thanks in advance. ...more >>

What is the usual way of designing a database with multiple data types, each of which has its own set of attributes? For example, computers (hard drive size, cpu, memory, etc.), monitors (size, resolution), printers, and so on. I can think of several ways, but there are probably pros and cons...more >>

Hi I have a few questions about data files. 1) Is there any way to know how much of a data file is used up with data and how much is still available ? For example if 7mb of a preallocated 10mb data file has data in it, is there any way I can see that 70% is being used and 30% is still free ? ...more >>

Does anyone know how to make a sql script that deletes every view that starts with the letters A1 in the name. I understand that I have to use the DROP VIEW command, but that is how far my knowledge goes. Thanks! ...more >>

Does MS SQL 2000 support ON DELETE RESTRICT when creating a foreign key? TIA Carlos San Miguel...more >>

Hi All, I'm wondering if it's possible to base a CREATE TABLE statement based on another existing table? In MySQL you can use CREATE TABLE MyNewTable LIKE MyOldTable and this will create an exact duplicate of the structure, empty of any records in the old table. I can't find a similar sy...more >>

I'm using a stored procedure as the record source for a MS-Access2K form: Forms!myForm.recordsource = "spName" The SP uses some User Defined Functions and sometimes it fails. Is there a way to write an error handler into the SP that will ignore the data error and continue so I can populate th...more >>

I'm trying to Backup Transaction Log for a Database however it fails with SQL State 42000 and ERROR 22029. Ive viewed loags and Histories but they offer only error numbers provided above. I tried the same thing on the NorthWind DB as well but it failed too. Any ideas? Craig...more >>

I am having a strange problem with the SQL7 DTS Wizard. I need to export some data to send to a customer and the customer required ASCII Fixed File formatted files. I have exported a table and am trying to make sure the data will successfully import. The table has a couple of DateTime columns. T...more >>

I have a table that contains stock market trades. I need to count up the different types (sides) of trades for a given security in a given account. I am looking for the results to be on a single row. (For example: In the 123 account with the XYZ symbol how many buy trades and how many sell trade...more >>

I'm wondering if anyone has any advice about using a Linux/Apache web server with a MS SQL Server back end. The decision has already been made that the web servers must be Linux/Apache - but I don't think MySql is going to scale well enough - has anyone successfully used MS SQL Server with Linux...more >>

I have created an application with an MSDE database and an MS Access interface. I now want to move this application to another standalone PC that will be the 'production environment'. The production envirnoment doesnt contain enterprise manager or query analyser so I am flying blind, using sto...more >>

All, I am using all the necessary criteria for the openrowset procedure in MSSQL to execute a select statement against a server and database about which my procedure knows nothing. I would like to use the select statement passed into my procedure to build a temporary table (select * into ...more >>

I am currently running a website using SQL and ColdFusion. Unfortunately, the SQL database is getting fairly large for our web server. Right now it is at 70MB, and I'd like to decrease this as much as possible (without deleting content). Since I don't have much SQL experience, I thought I wou...more >>

My table looks like this Date Data1 Data2 10/03/04 A 123 10/03/04 A 456 10/03/04 A 7 11/03/04 B 222 11/03/04 B 333 I want to make a new table to look like this ...more >>

Hi all, Has anyone created a linked server to Remedy and connected successfully? I have created a linked server using the ODBC AR System driver but i keep getting the following error: IDBInitialize::Initialize returned 0x80004005 Any ideas? Thanks Sergio Australia...more >>

We have a few stored procedures that use temporary tables. When we do a showplan on these procs we get the following error: Invalid object name <Temporary table name>. Below is sample DDL that demonstrates the problem. (Note it is the insert statement that generates the error) Is ther...more >>

Is there a way to format a float number according to an arbitrary locale in TSQL? For example, can the client send a locale identifier of some sort, and have the server send back a calculated value (for example "3.5 hours") formated according to the client's decimal separator etc?...more >>

I'm a SQL DBA but have only dealt with databases less than 1 TB in size. Some of my programmers are asking how well SQL 2000 will handle a 5 TB database. We're wondering if SQL is sufficient of if we need to go to Oracle. I know the SQL 2000 specs say it can handle it but I'm looking for real...more >>

I have the columns created successfully for a full-text catalog. Here are my statements: CREATE TABLE tblTableName ( NameID int IDENTITY Not Null, FullName varchar(50) not null CONSTRAINT PK_NameID PRIMARY KEY (NameID) exec sp_fulltext_table 'tblTableName', 'create',...more >>

Hello all I am calling a vbs file using sp_cmdshell, it works fine untill it runs into an error then hangs. How can I trap this error in VBscript so that it can be returned to my SP and processed?...more >>

Hi I need execute "use" dinamic sentences. This code: BEGIN DECLARE @ESTADO VARCHAR(2),@MSG VARCHAR(255),@MI_BBDD VARCHAR(10), @MI_APLICACION VARCHAR(10),@MI_USER VARCHAR(40) SELECT @MI_APLICACION = 'VIVA' SELECT @MI_USER = 'DPEREZ' SELECT @MI_BBDD = BBDD FROM RTCM.APLICACION WHERE COD...more >>

I'm trying to query a second instance on a SQL2K Server from a SQL 7 system. How do I fully qualify the second instance within the query? SELECT * FROM server\instance.dbase.dbo.table The above query returns an Incorrect Syntax near '\' error. I have linked the servers on both systems. ...more >>

Hello all I am calling a vbs file using sp_cmdshell, it works fine untill it runs into an error then hangs. How can I trap this error in VBscript so that it can be returned to my SP and processed?...more >>

Hello, As a newbie to VBA I am having difficulty setting up the code to call a stored Procedure from MS Access 97. I have already written the stored procedure and tested it and it works fine. The stored procedure requires two input variables, both formated as datetime. These variables are used ...more >>

Dear Friends, I have created full text catalog and index for a column named 'img_file' of datatype 'image'. I am storing all the MS word documents. I have a column name 'img_type' which stores the extension of document file as 'doc' for all the documents. I have created fulltext indexes via...more >>

Hi folks... I'm wondering if SQL Server 2000 has some kind of a feature that will automatically alias fields if the query returns multiple fields with the same name. For example: SELECT * FROM table1 INNER JOIN table2 ... Let's say table1 and table2 both have a field named "TEST"; well, t...more >>

Hello How I can insert string like this one (with " and ' inside - simply: other insert expression) -- start -- exec sp_executesql N'INSERT INTO \"base_name\"..\"documents\" (\"Id\",\"Type\"")VALUES (@P1,@P2)' ,N'@P1 int,@P2 int', iden1, 16 --end -- to MSSQL ( exactly SQL CE ) Konrad P...more >>

We have a small problem in our shop. The servers are managed by a central support group, while SQL Server is managed by a DBA in development. Because Microsoft has so closely coupled the OS and SQL Server, the DBA often needs access to Control Panel functions in Windows (starting & stopping ser...more >>

How to close a connection in a DTS package after a certain step? I got a DTS package that loads some data from Excel into Access database. I need to add a step to it to compact the database after deleting data from all tables and before loading the new data. I added an "Execute Process Task...more >>

I've got a table full of HEX values which reside in a CHAR field. Is there any way to get their INTEGER value? CREATE TABLE _Hex (val CHAR(2)) INSERT INTO _Hex VALUES('0E') -- hex value for 14 This next line fails: SELECT CAST(val AS INT) FROM _Hex "Syntax error converting the varchar va...more >>

Hi there Getting very frustrated with this...maybe someone can give me some pointers where I am going wrong... Platform :- A.SQL 2000 SP3 B.Domino \ Notes 6.5 C.Outlook 2003 D.MS Outlook Connector for Domino\Notes Trying to do :- Get SQL Mail to work with mail profile using Outlook, v...more >>

Hi! The job of backing up database has been failed after running for almost 2 hours(never happened before). The message I got was: "Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." Can anyone give me a piece of advise how can I solve ...more >>

one really nice feature of A2K adp's was the ability to manipulate sql server security by using the interface under tools->security->database security. from what i understand that feature is missing from XP because ms didn't want users to be able to get by too easily using MSDE. what happens...more >>

I have created a table doing the following: CREATE TABLE TableName ( NameID int IDENTITY Not Null, FullName varchar(50) not null CONSTRAINT PK_NameID PRIMARY KEY (NameID) ) I tried to create a full-text catalog using Query Analyzer: exec sp_fulltext_table 'TableName', 'cr...more >>

Hi, My system administrator installed SQL Server 2000 and SQL Server 2000 SP3 in Winodws 2000 Server platform. I tried to use SQL Server Books Online through Enterprise Manager but the error message: Help Topic not found! showed up when I click "?" icon. I found the folder "Books" under \\Pro...more >>

I can't seem to come up with an elegant way of doing the following. I have a table as follows: Ident_column, JobId, JobColor, EnteredTime This table logs every time the JobColor is changed for any JobId. I want to return one row for each JobId, for a particular date range, that had the JobC...more >>

Hi there.... this is my setup: Brand new Windows 2003 Server running SQL 2000 with all patches. SQL server runs OK. Windows 2003 server is not a domain controller. My OLD W2K SQL server is a domain controller. Problem: No matter what local account on the new server I associate under MSSQL ...more >>

I have a query that is taking more than an acceptable amount of time to execute in a system that doesnt have much data in it yet, so I can only imagine what the performance would be like when this system goes into full production...Does anyone have any ideas on how to optimize this query? Se...more >>

Hello, I would like to change this sentence to ANSI format. SELECT A.COD_APLICACION, A.COD_COMPONENTE_LOGICO , B.COD_APLICACION, B.COD_COMPONENTE_LOGICO, C.COD_APLICACION FROM RTCM.COMPONENTE_LOGICO A, RTCM.COMPONENTE_LOGICO B, RTCM.APLICACION C WHERE A.COD_APLICACION_PADRE...more >>

I setup MSDE 2000 for Developers Using Visual Studio .NET using the command Setup.exe /qb+ INSTANCENAME=VSDOTNET DISABLENETWORKPROTOCOLS=1 SAPWD=mypasswordhere Then followed other directions to use Svrnetcn.exe, to assign port 850 to the SQL Server Desktop Engine installation. Then creat...more >>

Here's what happened: --Access 2000 (SR1) database linked to SQL Server 7.0 (SP4). --Access app contains bound forms and bound combo boxes. --Out of nowhere 2 weeks ago, client started getting timeouts and locking errors during a process that had been run twice daily for 2 years w/o incid...more >>

Hi, i would like to know if it is possible for me to get back the rows i am interested in rather than all the records matching the query. Right now my query returns first 20 items, 30 items or the first 500 items. But i am having a problem with timeout when i query all 5000 items. Can i g...more >>

In Access it is possible to create a Unique Index that ignores Null values, ie doesn't count 2 null entries as a conflict. There doesnt seem to be the same 'feature' in SQL Server. Is this correct? ...more >>

In Oracle and DB2 there is the concept of "Client Id" for a session/connection. This can be different from the actual database user id. The "Client Id" can be used as a special constant in a SQL statement (in my case I refer to it in a trigger that does table auditing). Is there any equivalen...more >>

Hi, Any easy way to add a row number into a table got contain data ? In my table only got 1 column. I know using cursor and looping each record, I am able to do it. Just want to know any other faster way to get it done. Example : Hello~world~record1 Hello~world~record2 Hello~world~rec...more >>

Do user multi-statement user defined functions go to tempdb or they processed in memory as a scalar function is? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hello, I can do basic SQL, but I am stuck on this problem. TABLE 1 : CAR STOCK DETAILS --------------------------------- SalesManName | Salesman Email | CarType | CarColour | NumberInStock | DailyEnquiryLimit --------------------------------- TABLE 2 : PENDING CAR SALES E...more >>

Good Afternoon, I store all of my users in a main table appropriately call 'users'. From there I have 3 sub table which contain their specific ID's as well as the user_id's from the users table. These talbe are 'administrators', 'demousers' and 'deactivatedusers'. This stored procedure is sup...more >>

Anyone run across this message before? I get this when trying to issue the sp_replshowcmds or sp_repldone commands. I even stopped SQL Server Agent, tried it and got the same error....more >>

Hello there, I've to import datas into sqlserver from some .idx files and I wonder they are foxpro tables. But I only have the .idx files, and not foxpro, so I don't know how to do: impossible to directly open them with sqlserver or access (they want .dbf). Does any of you know a tool...more >>

To those of you that think there are 'no stupid questions'... I am attempting to deploy a VB .NET application that runs against MS SQL 2000. I need to find out what I need to do in order to deploy without having to install SQL Client manager to establish the required definitions for the SQL se...more >>

I'm having a major and immediate problem with the HOST_NAME function. In a multi user environment, at least one other machine is returning the name of my machine using the HOST_NAME() function, though others are not. I am in a major bind with this problem... Any help is greatly appreciated, lq...more >>

Hello, I have a table that has a text field. I want to parse only a certain portion of the text field. Anything followed by a special character say ? or # or $ to the end of the the field Thanks...more >>

This statement is part of a larger SPOC I'm debugging. The error in SQl Query Analyzer is: Server: Msg 145, Level 15, State 1, Line 3 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. But I have all the order by items in the SELECT list! It looks like the CASE ...more >>

Hi My database is 30GB When i drop some tables, one was 4GB, and shrink database, size of database remained 30GB. Why database is not now 24GB? Thanks alex *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

I have a text file which contains the following string format for dates/times: e.g. 1990-09-27 0919 I tried using the CONVERT() function to convert it back to DATETIME when inserting it into a table in SQL-Server, but I keep getting an 'Arithmetic Overflow' error. What is the appropriate...more >>

I HAVE A Table CALLED (TEST) COLUMN NAME--- SERVERIP AND CLIENTIP Each time a new record is inserted into either column the trigger fires and then will add those new record(s) into these 2 tables in the Serverip & ClientIP columns SERVERIP COLUMN NAME-- Version ID & ServerIP CLIENTIP ...more >>

Hi I have two sql server 2000, instalations , one under windows xp other under sql server 2000. In sql server 2000 all run ok In xp I don't insert, update column datetime, all times I receive: Server: Msg 241, Level 16, State 1, Procedure PR_INS_USUARIO, Line 17 Syntax error convertin...more >>

SQL Server 2k 1. CREATE PROCEDURE iwms_sp_AddVehicle @vehiclereg varchar(12), @vehicletypecode int, @site int as insert into iwms_tbl_CustomerVehicles (VehicleReg, VehicleTypeCode) values (@vehiclereg, @vehicletypecode) insert into iwms_tbl_CompanySiteVehicleAssignments (VehicleReg,Comp...more >>

Relatively new to SQLServer. Trying to access the current logged on user name from a View (Criteria: =CURRENT_USER). All I get is "dbo". What am I missing?...more >>

Hello, everyone: We have a Oracle 3 tier application (using Oracle iAs to a back-end Oracle database on an Tru64 UNIX server) which also incorporates custom written analysis and mapping tools for fisheries-related data. There is interest in broadening the scope of this application to also g...more >>

Hi, I would like to ask how to convert timestamp column of SQL Server 2K to datetime, or any date string? I use it as checking update time of table records, it work fine except that i don't know how to convert it to datetime for display. Anyone know how to do this? Thank you very much! ...more >>