hey all, what's the best way to get all the records in a master table and a sum of a column in a related details table? thanks, rodchar...more >>

Hi, If I have a table with the following structure/data: CommentID Comment --------------------- 1 YELLOW 2 BLACK 3 RED --------------------- I want to produce resultset that looks like this in a SELECT statement (3 header columns and 1 row of data). ...more >>

Hi All, Here is my situation: I have a table which has a column of type datetime and it carries data with timestamp in it. For example: 2004-08-16 16:09:56.120 I have another column which is also of type datetime but contains data with no time values (because someone didn't pay much atte...more >>

Hi, Could you help me modify the SELECT statement below that would accomplish my final goal. SELECT col1, col2 FROM table1 WHERE col3 < @V In col1 rows have many duplicates col2 has unique values col3 can have 5 possible values: 5, 10, 15, 20, 25 Variable @V can be equal to 5 or 10 or 15 o...more >>

Hi, Could you help me modify the SELECT statement below that would accomplish my final goal. SELECT col1, col2 FROM table1 WHERE col3 < @V In col1 rows have many duplicates col2 has unique values col3 can have 5 possible values: 5, 10, 15, 20, 25 Variable @V can be equal to 5 or 10 or 15 o...more >>

We are having problems with the speed of our SQL Server. One of the things I'd like to look at is the changes in the various indexes and relationships. Is there any easy way to get the structure of the entire db, or do I have to just go table by table and use the Ent Manager "copy" command? -...more >>

I need to determine a way to search if a tempory table exist before attepting to read a record from it. The only way I know of is to perform the following: if exists(select * from tempdb..sysobjects where name like '#tablename%' and type='U') However, if there are multiple sessions on...more >>

Hi everyone, I came across an opinion that clustered indexes shouldn't be used in busy OLTP systems. What are pros and cons in that? Thanks a lot in advance Alex ...more >>

Hi I have been investigating methods of generating an alert on a users desktop from a SQL Server 2k Trigger. This will only be a small scale application as we are a small company of 18 users and it will alert 2 users 1 - 20 times a day, but the alert requires almost immediate response fr...more >>

Is there a file size limitation to attachments using the master.dbo.xp_sendmail procedure? Thanks Ross ...more >>

I am getting an error with a stored procedure, seems to have something to do with the "type" of the field (text), here is my code: CREATE procedure Addtemplatestocase @FirmId int ,@CaseId int , @SecLtr nvarchar(50), @LtrName nvarchar(100), @LtrBody text as INSERT INTO LetterMrgs (FirmId...more >>

Hi all, I have a table called Mailbox in our database that stores e-mail messages. We store the e-mail attachment files in a folder called Attach. When certain field values change in the Mailbox record, I need to copy its attachments from the Attach folder to another folder. I have been a...more >>

Can anyone recommend one? TIA, Bryan ...more >>

I am having a problem importing data through the DTS. How can I force the DTS to insert numbers from a number field in Excel into a number column in SQL Server. I have a column which looks like this: sig_figs blank cell blank cell blank cell 2 blank cell blank cell blank cell blank ...more >>

Hi There I have a sproc which performs fine on a normal database but runs slow on a database with a lot of records. I think it might be because 'EventSurvey.GeographyID' is not a key? Any ideas on how to enhance the performance or what I might be doing wrong? Thanks. Declare @PKID int...more >>

What is the best way to show current time? Thanks ...more >>

What is the best way to merge two really large tables (7,000,000 rows each, and very wide)? The table definitions are the same for both tables. Right now I am using a insert into statement with a selection from one table at a time, but it takes way too long. I don't need it to be logged. A all ...more >>

Hello, Is there a way to do something like this update MyTable set BitColumn1 = not BitColumn2 I know that we can break it down to two different statements but I think there must be a way to do it in one statement. thanks for your comments and advice. ...more >>

I have a situation which seems like it should have a fairly simple answer... but I'm not sure how to do it. I want to create a view that joins several remotely distributed tables. Something like: CREATE VIEW AllData AS SELECT * FROM Server1.SomeDB.dbo.DataTable UNION ALL SELECT * FROM S...more >>

I have a rather complex SP that creates a #temp table and then populates that table using several select/inserts/update statements. I need the results of this #table to be exported to external text file nightly so that our mainframe FTPs can grab it. DTS apparently wont let me use #temp tab...more >>

Hi Is there a way to find the name of an identity column in a table using transact-sql. Are identity columns marked anywhere in system tables? I happen to find the column name in information_schema.columns, but am not sure how I can determine if it is an identity column. Many thanks S...more >>

I have a rather complex SP that creates a #temp table and then populates that table using several select/inserts/update statements. I need the results of this #table to be exported to external text file nightly so that our mainframe FTPs can grab it. DTS apparently wont let me use #temp tab...more >>

Is there away to determine the last time (date time) a stored procedure was modified within a SQL Server 2000 database?...more >>

Does any body know if the connection.exec has a size limitation. I am sending 30k of text through connection.exec and I get rusults but if I send 50k it does not work. It's almost as if the connection.exec can't handle the extra 20k. Any help would be great I have a production app down. ...more >>

We have a couple of apps running off of one SQL Server. For some reason, the server got swamped yesterday. I got a script to see blocking processes: http://www.devx.com/tips/Tip/13711 and I have scripts to see how many connections from which logons, etc. However, it really does not tell me ...more >>

Hi, Does anyone know of a quick an easy way of convertiing unix date serials eg 12815 to March 31 2005). Any pointers appreciated Thanks Simon ...more >>

Hi to all, I am a real novice to SQL and I am trying to use some tutorial. Here is the problem I cam across: There is a table with gene_ids (?gid?) and clone_ids (?cid?). Each gene only resides on a single clone and each clone may contain multiple genes. How do you find how many genes are...more >>

Is there a way to have an "autonumber" on a non-Primary Key column in SQL Server? Thanks, Eric ...more >>

HI Freinds, SQ 2000 I had a transaction replication before with a linked server. I stoped the replication completely and now I am trying to drop linked server. But it doesn't le me to do so, Complains about the server still in replication error 20583 How can I drop te linked server ? ...more >>

Hi, When is SQL Server 2005 being released to manufacturing? or At least the expected release date? Regards Habib ...more >>

Is there any other choice? Here's what I'm working with. I have to concacentate together a series of description fragments into one value. The table I'm working with has a primary & foreign key, as well as a field containing the 'line' number, and another containing the 48 character descriptio...more >>

I have a query the selects a days worth of processed work from 3 joined tables. I need to randomly select 100 rows of that data and insert them into another table. Can anyone help me with an EASY way to do this? ...more >>

Hi, This is kind of following on from my last couple of posts regarding Identity columns and so on. Basically, I want to ensure that for a particular table, every row has a numeric reference. This reference must be unique and gapless. Ideally it should order in the sequence of the records b...more >>

I am have a report that compiles response times for first responders which works fine. But what I need is to be able to only consider the responding units that get to the scene first. Example. Currently if 2 units are dispatched and one gets on scene in 4 mins and the other in 6 mins, our respon...more >>

hi, why I can do this: DECLARE @table_name varchar(100) SET @table_name ='ABC' DROP TABLE @table_name Line 3: Incorrect syntax near '@table_name' ...more >>

Hi, My apologies if I am out of topic. I was wondering if there is a small script, program or web reference that would explain the syntactical differences between TSQL and Access SQL. TIA -steve ...more >>

can you have SQL 2000 & 2005 installed on the same machine. What I am really getting at is having SQL query analyzer 2005 installed on my test machine along side 2000. 2005 QA allows you to print in color. Thanks ...more >>

While working with a database that contains subcontractors I came across an interesting scenario with the respect to their addresses. Currently all fields for the required data are in the one table and is causing some problems with regards to updating the redundant info across multiple entries. ...more >>

I have a procedure that is not executing for some reason. It makes no sense. It is generated by Asp.net. exec sp_executesql N'AddNewPosition2', N'@clientID varchar(20)', @clientID = '1234' Gives me an error: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'Ad...more >>

In my estimated execution plan for a UPDATE it says I have a 55% cost to do a "Clustered Index Update/Update". What is odd is that I am not updating either column in the PK/Clustered Index. Now I know this is the estimated execution plan, but why does it say this? The real truth will be...more >>

I have a company table that lists the company name, fiscal year, and fees. I am trying to compare the last two years without specifically setting the criteria because I want the data to drive the comparison between the two. Adding to the complexity is the fact that a company can have...more >>

I am having trouble with the following view in SQL: SELECT CAST(AVG(L) AS Decimal(9, 1)) AS LAvg FROM dbo.HOSurvey_holding WHERE (L <> 0) I want one decimal holder but when i have a whole number it doesnt put a .0 after it? is this possible if so how would i do it? The data i have...more >>

I am writing an examination system and need to calculate total marks for exams for candidates. Sounds easy! It would be apart from exams where there are optional questions within a section, e.g. "Answer 3 questions from the 5 available". Each of the questions would have the same maximum numb...more >>