We've been having a lively debate, at my office, about the pros and cons of using SELECT INTO versus CREATE TABLE/INSERT INTO when populating temp tables in SQL Server 2000 stored procedures. Without stating my opinion, I'd like to hear what others think. Ideally, I'd like to see links to supp...more >>

I have a pretty large query. It is selecting a distinct 2000 records. If the query returns 2000 records...it takes 11 seconds to run. There is a single order by clause on one column...a datetime column. The query execution plan shows the SORT as taking 75% of cost. Taking out that order b...more >>

Hi, When really should I use Char(n) Varchar(n) NVarchar(n) I have seen many definitions but I need an expert to confirm. I know NVarchar in non-unicode, however, what is this unicode and non-unicode. Spare my ignorance. Oh... and Joe Celko if you happen to spot this, try not to bang ...more >>

Is there some sort of command that can tell me which mdf and ldf files are associated to which databases? Thanks, Ninel -- Message posted via http://www.sqlmonster.com ...more >>

I have webspace running on a machine with IP address 82.165.206.115 through 1and1.com. Then I have an internal server running on my network which is running SQL Server 2000. I have yet to be able to make a connection to my internal server from my webspace. I have forwarded port 1433 to the se...more >>

HI I set up two remote sql2k5 server, one uses windows 2003 server standard, one uses windows 2003 server R2. i stalled sql2k5 on both server the same way (both has sql2k5 sp1). i can't connect to server 1, but not server 2 (see error below). i checked the sql2k5 server configurations and ne...more >>

I've got a challenge -- I have a one-to-many relationship - files to filecategories -- and but only want one row back per file, not matter how many categories it fits into. Distinct won't do it, because I have text columns in the query, and casting them as varchars doesn't help - SQL 2000 st...more >>

I would like to query this table below to get the 2 largest op numbers and depts per part. IE for 11551 I'd like to return 11551 1730 100 11551 1730 110 etc Then I'd like to build an Exception table (with part and operation and dept) to be able to to override the above and give an...more >>

Have two tables order and orderline. Is there a way in T-sql to get an inner join to return all order records but only the first orderline record of each order? Sort of like: Select * from order inner join top 1 orderline on <join condition>..... Thanks. ...more >>

How can I make my SQL Server 2000 public? I'm moving a web application to another domain and I need to connect to it via my SQL Server IP Address. In Query Analyzer in my PC (where the DB is located), I enter my IP address and my user/password, but it doesn't connect. I have two instances: COMP...more >>

We are currently having the following process finger-pointing issue... Assume you have the following situation... An Orders table A Shipments table An Invoice table A separate program takes Orders information and Inserts rows into the Shipments table (at time of shipping) When rows ...more >>

Hello, How can I return only the numeric chars of this data? CREATE TABLE #temp1 (col1 varchar(10)) INSERT INTO #temp1 SELECT 'abc123' union SELECT 'defg1234' Thanks Rich...more >>

I'm new to implementing transaction and can you help in this manner. I have the sp below. I use @@Rowcount to see if the name is already present in the database and if so pass a message to the program. Thanks for the help Stephen K. Miyasato CREATE PROCEDURE REG_InsertPat --Will che...more >>

I understand that if the code assiciated with a trigger fails, then the action that called the triger in the first place fails as well. Any way to circumvent this (and not have the underlying action fail) ? ...more >>

I created a function and passing in a table however, it compains, Can we pass in a table to a function?? anhting wrong with this? can't see the error thanks Ken CREATE FUNCTION dbo.getLatestDate ( @Original TABLE (Indx INT, ID INT, iDate DATETIME) ) RETURNS @Result TABLE ...more >>

Hi, Is there a way to tell if an index has EVER been used on a table (apart from going back in time and setting up a trace for it)? Thanks....more >>

Ok, I've got two tables, Clients and History. The relevant fields look like this: Clients ----------- ClientID AccountNo History ----------- AccountNo Year Value Each ClientID can have one or more accounts, so multiple records in the clients table for each clientid. Each account ...more >>

Hello, SELECT CASE replace(@s,'sec','') WHEN 12345 then 'y' ELSE 'n' end returns 'y' I want to test if the numeric portion of the string is < 15000. If I put 15000 in place of 12345 then the statement returns 'n' - only tests for =. How can I test for < or > and still return a 'y' if...more >>

If I do the following withing a trigger: declare @SQLText NVarChar(4000) set @SQLText = N'select *, cast(''I'' as varchar(1)) as FlagUID into ' + @TmpTableName + ' from Inserted' exec (@SQLText) SQL Server tells me that "Inserted" is not recognized anymore... This is the error mes...more >>

Can I create a function that can be a part of a SELECT statement and it would be capable of accepting a parameter that is a column name from one of the tables in the statement? Thanks ...more >>

I have a table defined inside a function. Can I index it? If so How? please give me a simple sample code thanks ke n ...more >>

When I set breakpoints in the Query Analyser Debugger, they don't work. (The Stored Proc is executed but the debugger doesn't stop on my breakpoints!) How can I make him stop on my breakpoints? ...more >>

Hi all, Is there a quick way to pull table Schema of a table? Like to see the name, datatype, size thanks gv ...more >>

I like the current configuration of my SQLServer instance which, I guess, does case-INSENSITIVE text comparissons. However, inside a few of my StoredProcedures, I need to specifically do case-SENSITIVE text comparissons. If I want to do something like: ...where(@SomeVar = SomeColumn) and h...more >>

I have the following cursor process. My syntax joining to another table is failing. Is this possible??? Joining to another table to get a data value for the update within a cursor??? WHILE @@FETCH_STATUS=0 BEGIN UPDATE DMD_Data.dbo.custom_data SET file_id = GEN.file_id, field = @ac...more >>

I have to INSERT Lots of Values from an Excel spreadsheet to a #temp table and then use the valus in this #temp table to update rows in our database. How can I INSERT LOTS of rows, over 22,000, to this #temp table with the values that are in this Excel spreadsheet??? Thanks in advance for ...more >>

Hello Experts, I am trying to create a Stored Procedure in SQL Server 2005 from a query that works fine in Access, but it does not work on SQL Sever and returns a empty table. Any suggestions. Thanks. Richie =========================================================== SELECT tb_Question...more >>

We have a Invoice column which has a varchar datatype. the data is very messed up. there is an int data(14786555), char data (letter) and also empty field in a Invoice column. I am trying to run a query that only bring int data and not bring empty, char and alpha numeric data. anybody has a...more >>

Hallo NG, Consider the SQL queries attatched below. I have two versions each for two different tasks I want to perform. The one task performs either an insert or update, depending on a condition. The other task performs a delete. I've used the "Display Estimated Execution Plan" option in MS...more >>

I am running SQL 2000 SP4. I submit the following syntax for the check constraint but when it is in SQL it is changed and all my parantheses are gone except a couple. Weird. Please help. Syntax going in: CONSTRAINT CKFulfillmentPackage CHECK ((ResponsibleForCustomerCon...more >>

Hi, I need to get the last date of when stock levels hit zero so I can then do a datediff to calcualte days out of stock. Here is a table with sample data productid,stocklevel,date 1,10,1/1/2006 1,0,2/1/2006 1,0,3/1/2006 1,100,3/1/2006 2,0,1/1/2006 2,0,2/1/2006 2,10,3/1/2006 Desir...more >>

I would appreciate hearing some different points of views on this subject. Are / how are you using them? Are you avoiding them? Why? Are these so different to the extended stored procedures of 2000? I'm looking into what's new in 2005 before we start migrating our software - I've found ...more >>

Are there any problems rounding up dates like this DECLARE @FiveDaysPrevious DATETIME SET @FiveDaysPrevious = '12/12/2006 2:12:12' SET @FiveDaysPrevious = DATEADD( HOUR, 23-datepart( HOUR,@FiveDaysPrevious ), @FiveDaysPrevious ) SET @FiveDaysPrevious = DATEADD( MINUTE, 59-datepart( MINUTE,@...more >>

Analyzing an environment I noticed that there are lots of udf and sp that use cursors. The only action that is done is "close cursor" but none deallocate. Do you think would be godd or great idea to add deallocate at the end? Thx. ...more >>

How do I do the following in ONE query: I have a table, it has the structure of msgID, fromUserID, toUserID, msgContent fromUserID and toUserID are numerical IDs, linked to another table (users), which has a first and last names for each userID. What I'd like is to be able to send a query...more >>

Dear all, I want to limit a select sql statement for specific month. e.g. for Jan it is : select * from table01 where tradedate >= '01/01/2006' and tradedate <= '01/31/2006' For Feb it is : select * from table01 where tradedate >= '02/01/2006' and tradedate <='02/28/2006' I cann...more >>

We knew that we could retrieve the structure of a table via INFORMATION_SCHEMA.COLUMNS. Is there a way to convert the structure into a DECLARE variables statement? For example: From this: Table_A ( f1 char(10), f2 integer ) To this: Declare @f1 char(10), @f2 integer This way, the d...more >>