Is there a way to create a trigger directly on an inline or multi-line table value function? I am trying to create a quick-and-dirty application using an Access Data Project front-end with SQL 2000 SP3 EE. Thanks. ...more >>

I put a clustered index on a table with 2 columns -nationalityid int autoincrement PK -nationality varchar(50) the clustered index is on nationality however; when i do a select i still get a record set back ordered by the nationality id...what am I doing wrong? -Jim...more >>

Hi all, I need to change a varchar from 35 to 50. In the SQL Server books on line it says that SQL Server actually creates a new table when you change the length. I ran a test in a test database and it appears the only thing that changes is the length. All the data remains in tact. The table...more >>

Greetings, Under which Registry key can I find SQL Server Name? MTIA, Grawsha...more >>

Hello there, This is somewhat of a newbie SQL question. What are the minimun databases SQL needs to operate correctly. I have inherited a SQL db and am not a DBA. The db's that exist are: Master, Model, MSDB, TempDB, Pubs, Northwind along with the databases I've created. It seems to m...more >>

Hi, I have a Excel sheet that have a column with mixed data: column can contain data like "892-234-32A" or like "892298343233432" I need to get all data column such as "text" to avoid numeric field show as exponential number. I'm trying with: SELECT CAST (CODICE as bigint(25)) FROM OPENRO...more >>

I got a big Access file (1 400 tables) to convert to SQL and I would like to be provided with some automated tools, except upsizing wizard and DTS, to convert it on my own. I got a lot of forms and query to convert too. Can someday provide me with at least one tool name ? Thanks, Guy...more >>

I need to update a table by copying a column from another table (having the same structure, but on another database), from the record having the same primary key. 1 - What is the correct query? 2 - I tried copying them record by record, but the datatype is ntext, (it displays <long te...more >>

Hi, I have write a stored procedure which makes update in a numeric (int) field. Depending on data in other table, in some case the result of query get a Null value instead a zero value... How can I tell to Update query to NOT update field if the value is Null ? I hope my word clear... ...more >>

Hi ng, I need some input/suggestions for a very small layout. The situation: Some groupings of thumbnails. For every picture (thumbnail) there is a "big" picture. Thats it basically :) On the front the scenario is this: A user clicks "Autumn". The user is presented with the "Autumn" thum...more >>

Hey I need to store something a little different in a DB and I was hoping one of you guys might be able to help me. Basically it represents a 'world'. I have an initial state and then I get info like this... 27/11/03 17:21 Mary is born 27/11/03 17:21 Dave is born 27/11/03 17:22 Sean is ...more >>

I was wondering if anyone could suggest a web-based help desk system that uses MS SQL server that also has a knowledge base and also allows support employees to log how much time they have spent on each particular ticket. I'm after an 'affordable' system that would be used by about 35 people. ...more >>

Hi All! I like employee and department scenario from DBDesgin Q. It looks more intuitive than my previous sample. *********************************************************************** Business rule: Each employee works only in one department. Department is managed by only one of emplo...more >>

we have ODBC drivers to connect to SQL Server from a unix box. I looked at cspan.org for Perl DBD/DBI with sql server and I don't see one. Is there a place from where I can download perl DBD/DBI for SQLServer. Thanks. ...more >>

I have an application written in VB.NET that connects directly to a SQL Server 2000 database over the internet. It works fine on the development machine (XP Pro), as well as two outside machines running XP Pro. It does NOT work on two other outside machines, both running Win2000. All 4 outsid...more >>

Hi I am looking to implement an audit/history table/tables but am looking at doing this without the use of triggers. The reason for doing this is that the application is highly transactional and speed in critical areas is important. I am worried that triggers would slow things down. I am...more >>

I have a basic question regarding rights. What level of rights do I have to have to grant another user update rights? I don't want to give everyone owner rights. Can a person with update rights grant another person update rights? Thanks....more >>

What is the result of adding an or into a left outer join? For example: Select l.list_id, l.request_id From List l left outer join Request r on l.request_id = r.request_id or l.list_id = r.list_id Thanks :)...more >>

Suppose the following tables: parent ------ parentid address phone . . <many fields> . . child ----- childid parentid name I want to select every column from the parent table and the count of the child rows grouping...more >>

Hi everyone, I'm a bit new til ms sql server and hope that anyone here can answer a question I have. I'm running a ms sql server 7.0. The server is accessed by a application written in Access (help..) and sometimes I experience that the server performance is slowing down. When I do a sp_who I...more >>

Hi, I've written a M.plan in my client place which is scheduled to occur weekly once.But 4 days gone from the last backup taken , there is a problem in the server which does not start loading windows .The only way is entering via SAFE-MODE.In the safe mode the E.Manage does not connected(is...more >>

I am trying to understand how the data in sql server is stored and also regarding fill factor and page splitting. 1) My first question what is the difference between Index pages and Data pages. and how are they different for clustered and non clustered indexes and heap tables. 2) What is t...more >>

Here's a weird one. I'm running SQL Server 7 and when I run a backup something weird happens. When I perform the backup via Enterprise Manager by right clicking on the database I want to backup, I click on OK but no progress blocks show up in the window showing you the status of the backup. ...more >>

Hello, I am interested in dynamically creating temp tables using a variable in MS SQL Server 2000. For example: DECLARE @l_personsUID int select @l_personsUID = 9842 create table ##Test1table /*then the @l_personsUID */ ( resultset1 int ) The key to the problem is that ...more >>

I am writing a download process in which i have a condition where i need to join four tables. Each table have lot of data say around 300000 recs. my question is when i am doing the joins on the columns is there any specific order i need to follow. for example My original query l...more >>

Hello! We have a set of individual .SQL scripts which we would like to execute against a MS SQL Server 2000. Is there any way to have ISQL utility (or any other means) to execute all of them without having to establish a separate database connection for each script: isql -Ux -Py -Ss -i scri...more >>

Hi, I am still not very proficient in SQLServer. So apology if the question sounds basic. We have a script to clean old unwanted data. It basically deletes all rows which are more than 2 weeks old. It deletes data from 33 tables and the number of rows in each table runs into few millions. ...more >>

How can I get the date of the first day of the month of 3 months ago in one statement ? (And with no time in it ??) Arno de Jong, The Netherlands. ...more >>

Whats the format to send to a datetime field to distinguish it as B.C. vs A.D. -Jim...more >>

Hi, Received the following error during index creation of the tables. The data & log files are set to 'unrestricted growth' and enough space available on the disk. Any reasons? ___________ Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocate new page for database 'Ultima...more >>

I have a table, TEST_TABLE, with 6 columns (COL1, COL2, COL3, COL4, COL5, COL6).... I need to be able to select all columns/rows where COL3, COL4, and COL5 are unique.... I have tried using DISTINCT and GROUP BY, but both will only allow me to access columns COL3, COL4, and COL5..... i need ac...more >>

Hi! I need to write a script that will remove any constraints and triggers from any table in a specified database. My problem is that i don't know where are stored in the system tables the constraints and relations... I've been told that once this would be found, i would just have to use cur...more >>

Hello - I have three feeds from sources around the world, each coming in at a separate time. These feeds move into a large table (3GB) that is queried by managers. The feeds are loaded sequentially, and then the previous day's feed rows are deleted from the table (this is done so that the us...more >>

Hello. Newbie on SQL and suffering through this. I have two tables created as such: drop table table1; go drop table table2; go create table table1( name varchar(10), code1 integer, code2 integer, code3 integer, code4 integer, code5 integer ); go create table table2( code inte...more >>

Hi everyone, Is there a system stored procedure or another way that I can retrieve the current date and time of the SQL Server computer? Any info would be appreciated... Thanks in advance, Dan...more >>

Is there any known SQL Server bug whereby a record can be successfully inserted and committed, but then later be found not to be in the database? For example, if there was a server crash just after the commit, could committed data be lost? I'm sure the answer must be "no", but a client is tel...more >>

SQL Server 7.0 The following SQL: SELECT TOP 100 PERCENT fldTSRID, fldDateEntered FROM tblTSRs WITH (NOLOCK) WHERE ((fldDateEntered >= CONVERT(DATETIME, '2003-11-21 00:00:00', 102)) AND (fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:59', 102))) returns this record: fldTSRID...more >>

Hi there... I use SQL server integrated security so when a user opens a database in access it prompts the username & password in a small popup box on connection, but I'd like to use my own customised form for the authentication process, is this possible? I do know that this login popbox is di...more >>

I inherited a SQL 2000 Enterprise active/passive cluster under AS. Both nodes run on identical HP DL580's with 4 Xeon processors and 8GB memory. The former dba attempted to setup AWE memory but missed a couple of fine points. The first thing missed: the boot.ini only has the /PAE and no /3GB ...more >>

Hello, Our QA team have running a lot of test scripts (for automated regression testing), they run them on the different databases (Oracle/MS SQL). Several of those tests are dependent on the current date/time. In order to be able to use them efficiently, we changed the current date/time on t...more >>

The database I'm currently working with is very old and some of the tables, SP, and views are not being used. I'm looking for a way to identify what items are no longer in uses, or what items are currently in use....more >>

I got a mdf file from my co-worker who just stopped SQL server and delete ldf file and give me a mdf file. if it was detached, it would work fine but it was just copied. Is there any way to attach this mdf file? I tried sp_attach_db and sp_attach_single_file_db and both failed. thanks,...more >>

my data is like so. Id Date Transaction 545 9/24/2003 3:01:08 PM 13051:10 546 9/24/2003 3:03:30 PM 13051:10 538 9/24/2003 2:53:31 PM 13051:1002 539 9/24/2003 2:54:57 PM 13051:1002 136 9/24/2003 10:08:45 AM 13051:101 137 9/24/2003 10:08:47 AM 13051:101 I wanna run a query ...more >>

Im trying to create a stored procedure that selects everything from a function name that im passing in through a parameter.. create procedure SP_selectall (@functionname varchar(25)) as select * from @functioname go I keep getting this error: Server: Msg 137, Level 15, State 2...more >>

Greetings! When I run the following SQL statement in Perl, I get an error stating: any help/pointers how this can be resolved? Thanks, -Murali SQL note_insert error: [109] [2] [0] "[Microsoft][ODBC SQL Server Driver][SQL Server]There are more columns in the INSERT statement than values...more >>

I had a problem with the transaction log size resently, where the log would grow >5GB in 3 weeks and SQL would crush. I created a dump log job that runs on weekends, and dumps the log. Is this a good practice, what kind of problem I can ran into, size of DB-40mb. SQL 2000 sp3...more >>

How does MicroSoft store the stored procedures in seperate files. What tools are used. Cause that's what I like to do too. Arno de Jong, The Netherlands. (SCPTFXR does not have the option to store the stored procedures in different files I think) ...more >>

I am looking for some examples of how to manage DDL scripts among various versions of a production db and development and testing. I have tried a few things in the past, and it always gets very muddled and cumbersome. I need to be able to build any version of the database from scratch, BUT I ...more >>

Is there a fairly quick and dirty way to shrink the file size allowed for a DB on SQL server after the DB is in use? Our old DBA allowed 3 gb for log files and we don't need nearly that much. However, the space is still being used and reserved. Can I shrink that "maximum" and clear up the spac...more >>

I want to add the content of a table into another I tried to copy all fields, except the primary key: INSERT INTO table2 (field2, field3, field4, ...) SELECT field2, field3, field4, ... FROM anotherDB.dbo.table1 gives the following error: Violation of UNIQUE KEY constraint.....more >>

Please, please help me !!!! I have a stored procedure that I need to modify. The stored procedure is used in an Access DB program where user selects a ProjectID and views/or can email data for different Units. I have to make sure that if a projectid is for ‘Fire' Unit report goes to EVERYONE...more >>

Hi I tried the following from the help file... When you create or modify a unique index, you can set an option to ignore duplicate keys. If this option is set and you attempt to create duplicate keys by adding or updating data that affects multiple rows (with the INSERT or UPDATE statement)...more >>

I have a table with two columns, Year and Price, as in the following example: Year Number 2003 45 2002 33 2001 40 2000 42 1999 36 1998 28 1996 24 1994 20 1990 18 I would like a query to return the following: 1. A count of the number of years, commencing with the current year, ...more >>

Hi I am a newbie to sql server and am trying to understand how the data in the tables are stored in data pages. As i am reading BOL it says that each data page is 8 KB and and the maximum amount of data contained in a single row is 8060 bytes. To test this I created a table test1 (c1 in...more >>

Can two entities have more than one relationship between them? A(A_ID PK,….., bID FK) B(B_ID PK,….., aID FK) A.bID refers to B.B_ID B.aID refers to A_ID 2. Does it make sense? Product (ProductID PK, Name,…, SpecialAttrID FK) Attribute (AttrID PK, Name,….,ProductID FK) Product c...more >>

Hi, As subj says. A simple menu is defined in a table. But how to extract it? Heres the deal: Table menu has these 3 columns: menuid parent menudesc Ok this should be easy right? Those menu-rows that has parent=0 is "toplevel". And so forth. An example: 1 0 "Items" 2 0 ...more >>

i've a database where relations are hold in a special way which the project leaders think of as "performant and uncomplicated" but which is very questionable to me: ------------------------------------------------ Table [Attributes] Fields [AttributeID] and [AttributeText] Table [Objects] F...more >>

Can anyone tell me or post a link that says how many global temp tables can exist SQL Server 2000? Also, is there a limit to the number of local temp tables that can exist? Thanks, Billy...more >>

The scenario: two tables CustomerTable --------------- CustomerID OrderID CustomerName CustomerEmail CustomerPhone OrderTable --------------- OrderID ProductID ProductName ProductCost This database was handed to me and I was asked to solve a problem - it looks like an inner j...more >>