Hi, I have a table (SQL Server 2000) with several date columns in it, all of which are individually NULLable, but in any one row, not all the dates can be NULL. I want a query which ORDERs BY the earliest date it finds in each row. I'm guessing I have to do this in two steps: ST...more >>

In SQL 2000, it's easy enough to return a delimited string as part of a resultset. Say, for instance, you want to display employee names along with a comma-delimited string of all the phone numbers each employee has. Typically you would first create a UDF as follows: CREATE FUNCTION Emp...more >>

Yesterday (Australian time) I posted a question asking about using user-defined functions from applications via APIs. The post does not show up now. Has it been deleted? Regards, Soloist...more >>

has anyone created a utility to search all tables in a database (or databases) for field(s) of a certain name (or LIKE matches) ? or, if not, has anyone cooked up the t-sql ? ...more >>

Hi everyone, while querying a sql7 user database using the following statement "SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'Column1' I get a value of 60 for a nchar column with the size of 30. According to the sql server books...more >>

Tables - Columns ------------------- acts - act_id (Primary), act_name, act_www shows - show_id (Primary), date, venue_id, notes shows_acts - show_id, act_id venues - venue_id (Primary), venue_name, city, state, venue_www SQL statement ---------------- SELECT shows.show_id, sho...more >>

Hi Pros I need to calculate weekly and monthly averages on my daily records in the table. The data looks as follows: Datetime Tag1 Tag2 Tag3 2003-01-01 00:00:00 4 3 2 2003-01-02 00:00:00 5 1 2 2003-01-03 00:00:00 0 4 0 ........more >>

I will be storing text in SQL db. In some cases it the text may be as little as 100 characters, in other cases it may be 5000 characters or more. Based on this situation, which is better - varchar(8000) or text type? What are the pros and cons or each? Thanks, Rogue Petunia ...more >>

Hi All, How do I write a sql script to do an auto insert a table of sample data like lname, fname, ssn, dob, etc. Is there any template that I can use to do that. Thanks, Tony...more >>

I am trying to create an instance of a .NET pooled COM object from a stored procedure using the sp_OACreate procedure. I am getting an "Invalid pointer" error. I have registered the COM object using the regsvcs, and I know I have the name of the component correct because I get a "not registere...more >>

hi this is my code.. SELECT b.id,b.start_time,t.type,b.of_sta FROM breakdown b INNER JOIN type_break t ON b.of_type=t.id WHERE b.of_sta = @sta AND b.end_time IS NULL i need to have a DISINCT on of_sta....something like that: SELECT b.id,b.start_time,t.type, DISTINCT b.of_sta FROM breakd...more >>

Hi All, I have this table: Name SSN (defined as CHAR(9) ) Jame Null Tony Null Bob Null Tom Null .. . .. . .. . I'd like to write a sql script to Update this table and automaticly insert SSN into SSN field like this: Name ...more >>

Hi there, We have created a function that returns a table of values for a specific id in another table. Now we wanted to join the result in the following way: ---------- create function functionB (@id as int) returns @resulttable TABLE (id int, value varchar(254)) as begin .....more >>

Hello All & Greetings. I am looking for ideas to design / implement a DB. I am working witha design but I am sure some one out there may have a fresh new idea that I can find more useful. Say, I am a small electronics store, selling Car radios, CD Players, DVDs, etc. Now, when a Customer co...more >>

MS SQL Server 7.0 Assuming the following tables: lookup -------- code char(1) result char(16) specificity code result ---- ------ A R1 B R2 % R3 data -------- rid amount code rid amount code --- ------ ---- 1 4.3 A 2 2.4 B 3 5.4 D 4 3.7 ...more >>

Is there any way to find out the database last access time ?...more >>

The inline and multi line versions of the table valued UDF all address the issue with WHERE clause using the parameter: CREATE FUNCTION fn(@sales, varchar(255)) .... SELECT ... FROM ... WHERE sales > @sales What about the FROM clause? I need to pass into the UDF the table name: CRE...more >>

HI if i write SELECT DISTINCT name FROM author it returns to me all the name distinct in the table, but i need to return all the sistinct name and more information in the same table... if i write : SELECT DISTINCT name, surname,age,city FROM author it apply the DISTINCT to all the row ...more >>

Hi there, I have 3 drives. One has the OS, the 2nd has SQL+database files and the 3rd is empty. I'm about to be run out of space on the DB drive. Is it possible to add a data file on the 3rd drive location, and thus avoid deattaching-reattaching the database onto a combined 2nd+3rd drive (aft...more >>

Hi all, Dynamic sql inside a trigger. This code dosent work in trigger any suggestion? set @sSql = 'select * from inserted' EXEC sp_executesql @sSql Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'inserted'. Aviram. ...more >>

I am running a stored procedure in SQL Server 2000 that contains an update statement on a table with multiple bit fields. I set up the update statement within the procedure to use the old values stored for the record for each field if the field is not explicitly specified in the parameter ...more >>

Hello, I'm executing a query to syscolumns table to retrieve the size of the fields of a table... Everything runs ok except when type is nvarchar, when the length field is the double of the real size. I've noticed that there's a field called prec that has the correct value. It's possible that ...more >>

How do I find the Service Pack number that has been installed on a instance of Sql Server?...more >>

Hello All Following is a resultset from cross tab sub query, the resolution is hourly i.e. Datetime Tag1 Tag2 Tag3 2003-01-01 00:00:00 2 3 0 2003-01-01 01:00:00 2 3 3 2003-01-01 02:00:00 2 3 3 2003-01-01 03:00:00 2 3 0 ......more >>

Hi is it possible to return metadata information from extended stored procedure? So when it is executed with SET FMTONLY ON metadata information will be returned from ESP. The reason why we need it is to be able to define a view based on extended ESP. It it possible to do the fol...more >>

I'm trying to figure out how to do this. So far I wrote a simple ASP script to correct a title but this isn't helping me much. It transforms "The Corrected Title" into "Corrected Title, The" just like how I want it. The reason it isn't helping is because it's too late. The query comes back...more >>

I'm new to / learning SQL Server 2000 and I have a question from a Web Development standpoint: I have a pretty good understanding of the necessity and workings of stored procedures for any database app, but I am having a difficult time understanding the importance of a View. I've been reading ...more >>

SELECT col1, CASE WHEN col3 = 'IT' CASE WHEN LEFT(col2, 1) = '0' THEN RIGHT(col2, 2) ELSE col2 END ELSE END AS col2 it returns me an error.. any help appreciated regards -- == fabriZio ...more >>

Can someone provide the syntax for setting a column to be an identity column in a stored proceedure. When I export my database, my identity columns are no longer identity columns. ...more >>

i have a not terribly complicated query, that if i run nativly on a server (ServerA), returns in instantly with 28 rows. If i run the same query on a separate server (ServerB), which is configured to use ServerA as a linked server, and i modify the query by prefixing all table names with "Serv...more >>

using sql 2k, I was able to set up my first indexed view. please see the code: create view vw_accts with schemabinding as select id, companyId, accountname,accountbal from account SET quoted_identifier ON SET arithabort ON go CREATE UNIQUE CLUSTERED INDEX IDX ON vw_accts (id, company...more >>

I plan to take the SQL Admin test (070-228)next month. I read the Microsoft Press SQL Admin book front to back. I am using the Measure Up and Transcender practice tests and finally passing all of them. I am a part-time administrator for 3 companies so I'm getting some hands on with BOL. How do I...more >>

sql2k sp3 Is there a way that I can do a case sensitive comparison on a case insensitive table? A word may be 'Bla' or 'bla' or 'blA' etc. They will not always be all upper or all lower letters. Do I need to make this table case-sensitive? Thanks in advance....more >>

hi there, I get an error when trying to query an indexed view over a linked SQL server. The error indicates the arithabort is not set correctly. Can someone give me ideas on how to resolve this, have tried many things. It seems that the linked server creates its own connection executes the q...more >>

I wish to transfer the data dictionary of a portable file of Powerhouse type in language QTP (extension.PSD)to SQL servor. Can you help me on the way of making it in Enterprise manage?? ...more >>

hi all, I am not very familiar with the locking mechanism in SQL Server. Inside a stored procedure I am doing this UPDATE table1 WITH(TABLOCK) SET COL1= 'value1' WHERE COL1 IS NULL I believe that the clause 'WITH (TABLOCK)' in the Update statement holds a table lock. I wanted to know...more >>

Hi, I am invoking SQL Agent job as "SQLAgentCmdExec". In this VB Application(Exe) is been invoked. It executes. But expected result is not seen.Below msg was found in job histroy. "The step did not generate any output. Process Exit Code 0. The step succeeded." My purpose is t...more >>

when I use select @@servername in my machine it returns NULL instead of the servername. What may be the possible reason...more >>

Is there a way to open QA from VB without having the connection screen. In fact I am looking in producing something similar to the Entreprise manager when using [Tools][Query Analyser], it open QA connected with the same credential as EM and connected to the same DB. I don't want to use a co...more >>

Can't use execute function in MSSQL user-define function I want to return a average number with top [n] record in a field of table, which [n] is a dynamic integer by user define. So, I design a user- define function in MSSQL Server that can input a [n] integer by user and return a average number...more >>

Hi SQL Gurus, I have Posting process using Stored procedure and use DML to do some updates. Here is my Simplified code. (DDL attached below and My Stored proc below) The PROBLEM IS : One user somewhere is Locking a Row in ORDERS Table, with this statement : Begin Tran Select * from ORDER...more >>