i have the following query: select top 10 bb.brand, count(bb.brand) as tally from participant p, beerbrands bb where p.favbeerid = bb.beerid group by bb.brand order by tally desc it will give me the top 10 brand, and count of that brand. Fine. But I need a 3rd column which is the percenta...more >>

Hi Sql Gurus, Written below are two queries returning the same result set. SELECT A.Col1 FROM TableA AS A INNER JOIN TableB AS B ON (A.Col1 = B.Col1) AND (B.Col2 = 5) SELECT A.Col1 FROM TableA AS A INNER JOIN TableB AS B ON (A.Col1 = B.Col1) WHERE (B.Col2 = 5) Above two queries see...more >>

Anyone know what the SQL is for getting relationships for either the entire database or for a selected table? Any help will be greatly apreciated....more >>

Hi All I am facing a strange kind of behaviour in sql server. I am executing a query like the following: select id, sum(amount) from (select distinct id,amount,xx,yy from purlistitems() ) as t1 group by id this query is taking a very long time [in fact i get a timeout on client]. when i ...more >>

I have eight memo datatype columns in Access which currently hold a max of 4300 characters each (not all fields are always used but they possibly can be), so when I convert to SQL Server, I won't be able to use varchar for these fields since they would make the row size too large. It seems te...more >>

Hello, I am trying to create a stored procedure that prompts the user and searches on any part of a field. I have tried using the Like criteria, but I can't get it to work correctly. Here is what I am using now: = @Enter_City Users have to type in the whole name of the city this way. ...more >>

I'm setting up a new database and am wondering if I should use bit or tinyint for a field that will indicate if a building has a basement. It would need to store 0,1,Null (Null if they are unsure if it has a basement). In C#, it would be nice to be able to have the IF statement look somethin...more >>

Hello, I am trying to update a record. If this update fails because no such record exists, then I would like to insert this record into the table. Would this be possible? Thanx, SK ...more >>

Hi all, I have a vb6 app that allows supervisors to arrange shift for their people. On the vb form there are Date, Start time, End time field which will be validated before saved into DB. What I want to do is to check is there any Overlap shift for an agent in DB, so I can prompt an erro...more >>

Dear all, Since my SQL server instance is in the name of (local). i am not able to configure the replication on my sql server. How do i overcome this problem ? Yours Sridhar K ...more >>

I need to pass in 2 lists/dataset to a stored procedure (from a .NET application). I don't think in TSQL, you can pass in a "DataSet" (like you could with Oracle), right? How could I get around this problem? I *can* pass in a comma seperated list of varchar, but there is a limit on the size ...more >>

What is the max length of text and varchar? I thought I saw varchar's max size as 8000 (not sure if right here). I want to store some long text that could be about 4000-5000 characters (including line feeds). I am curious as to which type would be the best to use and why (or maybe binary ...more >>

Even [firstname] + [lastname] or firstname + '-' + surname does not work Hope someone helps me out Thanks Ponnurangam ...more >>

I am trying this in sql server enterprise manager, does this has something do with the error Ponnurangam ...more >>

Even APPLICANT_FIRST_NAME + APPLICANT_LAST_NAME does not work I am getting the same error Thanks Ponnurangam "Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> wrote in message news:<u4vrIMsqEHA.1992@TK2MSFTNGP09.phx.gbl>... > Concatenating strings in Transact-SQL...more >>

Hi, I have three fields APPLICANT_FIRST_NAME, APPLICANT_LAST_NAME and APPLICANT_NAME While designing table in sql enterprise manager I am inserting "APPLICANT_FIRST_NAME & APPLICANT_LAST_NAME" for Formula Value of APPLICANT_NAME field But I am getting the following error "Error Valida...more >>

Any help would be appreciated. I have a field that may contain the same data twice with no delimitation. How can I identify these records. If I do something like: SELECT left(address1,5)As left5 FROM SYSDBA.Address I can get the 5 characters on the left. How can I check the remainder of the ...more >>

I will try and explain my problem with the following example: create table #tt (s1 int, st char(2)) insert #tt select 1, 'NE' insert #tt select 1, 'FL' insert #tt select 2, 'NE' insert #tt select 2, 'FL' insert #tt select 3, 'NE' insert #tt select 4, 'FL' insert #tt select 1, 'MN' ...more >>

From BOL Substring function topic: <snip> Note Because start and length specify the number of bytes when SUBSTRING is used on text data, DBCS data, such as Kanji, may result in split characters at the beginning or end of the result. This behavior is consistent with the way in which READTE...more >>

Hi, I've written code web-based uploading and downloading. Here is some code for it. For saving file into MS-SQL database, SaveFileIntoDB(HttpPostedFile file) { int fileLength = file.ContentLength; byte[] fileContent = new byte[fileLength]; int lastPos ...more >>

Can someone give me Examples where Non Clustered index is better than clustered Indexes Thanks ...more >>

While attempting to insert a large amount of data into a text field received the following error: Internal Query Processor Error: The query processor ran out of stack space during query optimization. The data being inserted contains several Char(10) values imbedded for formatting. If the C...more >>

Look at the following line of code, for use with the NORTHWIND DB... select employeeID from employees where employeeid in (select employeeid from customers) Obviously, if you ran the subquery by itself -- select employeeid from customers -- you'd get an error message: Server: Msg 207...more >>

Hi just wondering if there is a way to limit the size of a record returned from a stored procedure? The procedure has SELECT table.field1 FROM TABLE WHERE () field1 is a varchar 50 but I am sending the output to a dataset and then a crystal report and want to chop the characters at 2...more >>

I need to come up with a trigger that would prevent an insert if the value of the column "Logical_Name" is a duplicate of another with the same value in the "Table_ID" column. In other words, I have two tables (Table and Field) where there may be one or more instances of a field for one ta...more >>

I have to pass column name dynamically to a stored proc as I will not know the column name to be queried on, how can I write this SQL Thanks!...more >>

Thanks to everyone for all the great advices you have given me. This community makes SQL Server a much better environment than other RDBMS. ...more >>

Hi All I am not quite so good with SQL Server query so wondering if someone can help. I have a two tables one called empProfile which has fields UserId, FirstName, LastName and a table called holidayrequest which has fileds UserId, DateFrom, DateTo We store employee details in the...more >>

I would like to know more about SQL server programming. Could you recommend me a good book on that? I know basic stuff of database not SQL server specific. Thanks for your help. ...more >>

Hi all! One think with the MS SQL server that really gets on my nerves are the beautiful descriptions for dispatch errors. The other day, we got a IDispatch error #7786 (description: unknown error), and my boss insists that we find out why this happened. I looked around, and found nothing. ...more >>

Hi, I have two tables. One table (table 1) just has data for look up. ID Product_Name C01 product 1 C02 product 2 B01 product 3 ........................ The other table (table 2) has the data that need to get product name using the ID. However, the IDs in this ...more >>

Old Access db table has a character PK - example would be "C150-04" or "S405-03". The "C" and "S" indicates the type of specimen, the "04" and "03" tell me the year the specimen was taken, and the "150" and "405" tell me the label number of the specimen. Label numbers are pregenerated in numer...more >>

This is my first posting, and I'm not a SQLDBA, so take it easy on me. In an application I'm working on, we use stored procedures to permanently remove a record from the database. Our initial design had no cascading deletes turned on for a couple of reasons. 1. We were planning on only dea...more >>

I need to store several different fonts in a SQL Server 2000 db for a GUI (C#). What's the best way to store a Font (Family, Style, Size, Color) in a table column? Does it need to be a different column for each of these properties (string, string, int, int)? thanks -- Maddytk...more >>

Hi Friends !! The following query has been given me the result expected but it is not picking up the cluster indexes due to <> (not equal to) operand in the where clause. SELECT SO.ORDERID, OS.[DESCRIPTION], CM.FIRSTNAME, CM.LASTNAME, CM.EMAIL, CM.CUSTOMERID FROM TBLMASTERORDERSTATUS OS I...more >>

I am trying to create my first trigger and I am not getting the results I would expect. Could someone take a look at my code below to help? When a record is added to the company table, I want to add the same id and company name to another table (queue table). The id is not an identity...more >>

How would you update all rows in a big table in a batch. Create Table dbo.Test (col1 Int NOT NULL) DECLARE @i INT Select @i = 0 WHILE @i <= 1000000 BEGIN INSERT INTO Test VALUES (@i) Select @i = @i + 1 END NOW I wanna update all 1 M rows using update stmt UPDATE Test SET c...more >>

Here's the deal...I'm trying to rewrite a Crystal Report that made use of 11 subreports as a Stored Procedure. The user selects an Item Class, and the report spits out total quantity sold and avg price for each of the 11 customers on the report, for either the top 65 or top 125 most stocked items...more >>

How do I convert the results of a sql query to EBCDIC and export it? Thanks, ...more >>

I am importing data from a temp table into a production table and I receive the message that the import was stopped because data would be truncated. Normally, just a nuisance to find the offending data. But here is the rest of details: The field I'm importing *into* to has a datatype of nvar...more >>

I start a job using sp_start_job. I do not want to proceed without that job ending. How can I get the current execution status? I have tried to use sp_help_job and in job aspect "job" there is a column called current_execution_status. I want to wait until that execution status states th...more >>

I am trying to find the TOP 5 TIN by State. I have tried to figure out how to do this, but I'm evidently approaching it wrong. Can someone please help? I have a query like this: select state, TIN, sum(totalcnt) totalcnt, sum(totalcharge) totalcharge, case sum(totalcnt) when 0 then 0...more >>

Hello, everyone, I'm producing SQL scripts to recreate my database and storing them on my version control system. The scripts include INSERTS to the dtproperties table so my diagrams are recreated. The thing is that when I create a new database the dtproperties table doesn't exist. Can I cr...more >>

Can someone please point me to the information on how to write a search box into a page that will query a Catalog?...more >>

I have a program calling a procedure using the command logic that times out after 30 seconds. I have set the command timeout to 100 seconds and if I display the command timeout when the program fails after 30 seconds, the display shows that the command timeout is 100 seconds. Program vb ...more >>

I have a vb application which calls stored procedure sp1. sp1 inturn calls sp2. I have a situation where I want sp2 to return a recordset (set of rows) to sp1 and then sp1 will perform some action/filtration and return remaining rows to the vb application. ...more >>

I've been asked to query a database and compile a list of certain orgs. then every day thereafter, if there is a new org, have an automated email sent out to the new org. I was thinking of possibly writing a script that queries the database and stores the resulting orgs into a table in my...more >>

I've got a table of data that also contains an ID column for a bunch of look-up tables. For example, let's assume I've got 3 look-up tables (each of which will have from 2 to 20 possible values): Code1: Code1_ID int (PK) Code1_value char(1) Code2: Code2_ID int (PK) Code2_value char(1)...more >>

Okay, simple example of my issue. I have a table like this: IdentField Field1 Field2 Field3 1 Yada Other B 2 Yada Other C 3 Anothe Other B 4 Anothe Other C I want to do a SELECT query that gives ...more >>

I want to condense several rows of a table using a select statement, function, or stored procedure For Example mytable : 1 a 2 b 2 c 2 d 3 a so that my select should result in 1 a 2 b,c,d 3 a Any ideas or suggestions? ...more >>

In a scheduled stored procedure, before I do a bulk insert, I want to make sure the downloaded file has today's date. What is the best way to retrieve the date on a file? -- Thanks, CGW...more >>

Here is my code (Thanks to Mike): INSERT INTO dbo.SearchContent (SearchContent) SELECT RIGHT(CONVERT(varbinary(8000), dbo.WebParts.tp_AllUsersProperties),20) AS Data FROM WebParts Error: Cannot insert the value NULL into column 'Search_ID', table 'WSS_ContentDev.dbo.SearchContent'; co...more >>

I need to be able to make my stored procedure, so that if the user enters NULL as the @EmpSSNEntry, then it doesn't filter by the SSN. But if the user enters a SSN, it does filter by that entry. So if the user wants a complete list of employees, they just have to enter NULL, but if they want...more >>

Need some help! I have a stored procedure to update multiple tables. The table name increments by year (tablename2000, tablename2001...). I would like to use just one statement that will update each table until the most current table (by year) has been completed. This way I won't have to m...more >>

I am new to FTIs and I'm having a problem searching a text field in my database. I have a table that contains gene sequences that can be extremely lengthy (30K chars) so that is why I am using a text field. With that being said, I need to search this table using a portion of a full sequence wh...more >>

Hi, I am wanting to Sum a column but i don't want the rows that have a BalanceAmt = 0 to be included in the total sum. Goes something like this... Sum(Note)FaAm, Sum(RefAmt)Ref, Case When Balanceamt > 0 Then Sum(TopofNote - AH - Taxes - Insur) Else 0 End LoanProce, ...I ...more >>

Hello All, I have a database which is very highly used. I am currently experiencing a very high level of transaction deadlocks and this is becoming a problem. I have read a little about locking in BOL however as I'm sure you all know it's not all straight forward. Currently I'm looking a...more >>

I have a problem. I have a fairly big table of traffic records. This table store the record of all cars entering the premises. The table looks like the following: Ticket# Lic# dateentered Total_time 0011 aaa11 03/04/04 30 0012 aa...more >>

i am having the problem where the query analyzer simply hangs when i execute the following: begin distributed tran select * from [Linked Server].dbname.dbo.test1 commit tran If i execute the select statement independent without the transaction statements, it runs fine. Please provide so...more >>

Hi, I would like to perform the restoration of a database at a specific time. My backup procedure was : at time T1: BACKUP DATABASE MyBase TO MyTape at time T2: BACKUP LOG MyBase TO MyTape at time T3: BACKUP DATABASE MyBase TO MyTape If I want to restore MyDatabe à time T1_2, bet...more >>

Hi! I have a VB app calling a set of stored procedures under a transaction on SQL Server 2000. A trigger being fired by one of the SP's calls xp_sendmail and a simple info mail is successfully being sent (i.e. it arrives to the recipient). The strange thing is that the transaction I created f...more >>

I have data in this format ID ; Request ============= 1,A 1,B 1,C 2,A 2,B 3,A I want to change it to id reuqestA ; requestB ; requestC; ==================== 1 1 1 1 2 1 1 0 3 1 0 0 (the 1 and 0 re...more >>

I am new to SQL Server, and am trying to implement something that's fairly common and well done in Oracle that seems to be wreaking havoc in SQL Server... I have several queries that use the same calculation. This being the case I created a user-defined function to be called by the queries, ...more >>

But I am going to ask anyway cos it's driving me nuts!! How do I hide system objects?? I just can't seem to find the option anywhere!! Regards Marek...more >>

Hi, Can any one of you give the path to download the "Microsoft SQL Serverâ„¢ 2000 Analysis Services SP3". Thanks in advance Ragugct...more >>

You'd think there would be this, but it seems it doesn't exist? Is there any equivalent?...more >>

Hi to all gruop! How shuld be the select statment for this: I need to know how many sundays are between date1 and date2? Can any body help me? Thanks! ...more >>

I have table tmyTable like this. CREATE TABLE [dbo].[tMyTable] ( [IDTable] [int] NOT NULL , [IDxx] [int], [name] [varchar] (50) COLLATE Slovenian_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[tMyTable] WITH NOCHECK ADD CONSTRAINT [PK_tMyTable] PRIMARY KEY CLUSTERED ( [IDTab...more >>

Hello Everyone, I am trying to write a query that will pass in a value and pull back the JCode and the Qty used. So I have four JCodes Drug_Bill J1 - 50 J2 - 100 J3 - 500 J4 - 1000 Drug Id = 1001 I have done up my query and it gives me back the four records from the drug_...more >>

is it possible to script data for a sql server user table? I'd like to script all my table, including data like for example INSERT INTO MYTABLE (myfield1, myfield2) values ('myvalue1', myvalue2) INSERT INTO MYTABLE (myfield1, myfield2) values ('myvalue3', myvalue4) INSERT INTO MYTABLE (myfield...more >>

does anyone know, is it possible in T_SQL to do "alter table add [newcolumn]" but specify what order the column goes in, i.e. tell SQL server what column I want it to go before? You can do it with enterprise manager so there must be a way of doing it via SQL. ...more >>