I have the following code that I created today: select month(DataPesquisa) as Mes, count(FConceito) as Bom, isnull(Excelente.Conceito,0) as Excelente,isnull(Regular.Conceito,0) as Regular, isnull(Ruim.Conceito,0) as Ruim from satisfacao x left outer join (select referencia, count(Fconceito) ...more >>

I have a simple stored procedure that is defined as shown below: CREATE PROCEDURE get_books_by_title @title char(60) AS SELECT * FROM Books WHERE Title LIKE @title GO I call this procedure witha wildcard as shown: exec dbo.get_books_by_title '%' but this only returns 4 out of the 1...more >>

Hi, I have a table that contains orders from cliente. Suppose that each client has more than one order, I would like to write a SELECT statement that returns the order with the oldest date from each client. for example: Table: idClient idOrder orderDate 35 100 ...more >>

Hi all, I need a 'select' query that can give me the date of the first 'Monday' in a month for any given month, but am not sure how to go about this. Say I want to know the date of the first Monday in June ? regards, Colin H. ...more >>

All, How should I write the trigger on update? I'm sure the question is too simple. If I update only one record or a group of records I need to update the LastUpdate (datetime) field in all these updated records. How can I select only updated records in this trigger? Just D. ...more >>

I have a field within a table called customer with a field called address. tbl.customer.address 10 acacier avenue 11 acacier avenue 1056 acacier avenue Etc Etc I need some help to figure out how to separate the string at the first space so I may move the house number to a new field. A...more >>

Hello there I have srore procedure that do some changes on the database In order to make sure that all the changes will be together i using transation with SET XACT Abort It works fine on any errors and warnings, even on warnings that don't need to stop the process. In the code i also res...more >>

Dear All, In my Item Master table, the item column has values like 'printing' in more than 1000 rows. This was a data entry error. Now my user want all the instances of 'printing' to be replace by 'printed'. Is there any ways to update the entire rows in one go, or I will have to change the na...more >>

Hi, I have such a batch: /* some statements */ update T1 set F1 = some_expression where T1.ID in (A_BIG_QUERY) /* some statements */ I use this A_BIG_QUERY source about 3-4 times in this batch for different purposes and this A_BIG_QUERY requires a dynamic parameter to produce a result s...more >>

Thanks, it worked. Is there a way to add another column to the query that shows the total of 1 column: pieces volume Pieces Free Volume Free Invoiced TotInvoiced 1 1 0 0 10 50 1 2 0 ...more >>

Thanks, it worked. Is there a way to add another column to the query that shows the total of 1 column: pieces volume Pieces Free Volume Free Invoiced TotInvoiced 1 1 0 0 10 50 1 2 0 ...more >>

I want to create a table where the column names and number of columns is not known. It can be a temp table or table variable. Something like: DECLARE @output table (@columnname int) or ALTER TABLE @output ADD @columnname varchar(50) -Max ...more >>

hi pauls i am using ms-sql2000server on win2k server, i have three partition C,D $ E , C is NTFS format other two FAT32. my DB laying in C drive now i need to chage to D drive how can do it. Please suggest me suitable solution. Regards S Kaliyan ...more >>

Hi, When using an interface like ADO or ODBC, there is a call named "Prepare" which supposedly sent the statement to the server which in turn should parse it, save the query plan and return a handle to the client so subquent calls just refer to this handle/id and get faster execution in batch...more >>

Is there an easy way to count the columns of a table in T-SQL? Thanks for your help Sascha ...more >>

I have a datetime field that I want to display as m/d/yyyy hh:mm AM I tried the convert below but it did not return what I want. Thanks. CONVERT (smalldatetime, dbo.ActivityHistory.ActivityDate, 201) David ...more >>

Hi, Has anyone ever used the "CONTAINS" predicate when writing a dynamic SQL statement using sp_executesql? e.g. DECLARE @sSQL NVARCHAR(1000) SET @sSQL = N'SELECT Name FROM Employees E WHERE CONTAINS(E.Biography,' +@BioBlurb)' I've spent a whole day experimenting, unsuccessfully, tr...more >>

Hi, If i query multi-database and join them together, would there be any performance issues? Right now we have 3 differenet databases but they are related to each other. I would like to find out if there are any performance issues to query across three different databases Thanks Ed...more >>

I have a table1(key, field1, field2, field3...). My primary key is "key". Then i create a unique index for fields "field1" and "field2". This works, but i want thar sql server retrieve me the values thar are being duplicated. I'm using sql server 7.0. Can anyone help me??? ...more >>

I have a table contains comments. User scan create as many comments they want. my job is to combine and rearrange all comments in order of dates and time. acct date time Comments --------- ------- -------- --------------------------------------------- ...more >>

Gurus Is there a way to backup the standby database. I am logshipping from Location A to Location B Sever X, On Location B I have Server X as well as Server Y , But I need X to be stand by mode and Y to Be used for reporting mode. my idea was to backup Server X and Load that backup on Server Y...more >>

I am having a problem inserting data into a database table. There is a stored procedure that is attempting to insert data into several tables, if there is a duplicate entry already exists in the table then a unique key violation is thrown back from sql server (2000) to the data access layer. The...more >>

Hi, My question may be stupid, but can;t figure out a way to do it "simply". I'm taking over the admin of some SQL instance and I want to clenu up the logins list. But before deleting anything, I need to know what objects are owned by the user I want to delete to not brak anything. Is the...more >>

Hi All, I am trying to get some help of re-writting a piece of code as shown below SELECT code, description, clientDescription, band, AVG(rental) AS value, COUNT(tableTemp.surveyID) AS [count], CASE clientID WHEN @clientID THEN 1 ELSE 0 ...more >>

Hi, Would show me an SQL query to capture missing numbers from the consecutive order of numbers? For example, number 5 and 7 are missing from 1 to 10, how can I capture them? Please let me know. Onnuri ...more >>

People, Does someone has a function that calculates the correct weeknumber. My systems says that 01-01-2005 has weeknumber 1 instead of 53 and monday 03-01-2005 is weeknumber 2 instead of 1. Thanks in advance. Martijn de Jong ...more >>

hi, How am I able to get NT User Login Name? I Don't mean suser_sname()... because we have an application that always uses ReportUser to login to the database to retrieve data, but somehow we still want to caputure the NT Login Name since suser_sname() always return ReportUsuer... Than...more >>

I am trying to create a cross tab query to to combine the rows for two dates on one line: DATA: ( query to get this data: select top 6 stkhstCsiSym, stkhstDATE, stkhstXO from stkhst where stkhstdate >=20050225 order by stkhstcsisym, stkhstDATE) 1006 20050225 O 1006 20050228 X ...more >>

I have a table with a field of type datetime whose default value is getdate(). So, when a user entered a new record at '5/21/2004 11:10:10 AM', that value was automatically entered in that column. Now I'm trying to write a select query that will retrieve that particular record based on...more >>

Hello, I was wondering if there is an alternative for an array in sql server. What i would like (correct me if i approach this the wrong way) is an array which i can use in a where clause based on a variable/parameter. for example: using a select statement to retrieve a list of titles or...more >>

Using SQL Server 2000 and the following tables and values. ================================================ CREATE TABLE [Table1] ( [ID] [int] NOT NULL , [TelephoneNo] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [ID] ) ON...more >>

Does this statement applied to SQL2K as well ? Thanks. ....when you use the INSERT...EXEC statement to load a temporary table, as the stored procedure itself creates temporary tables, and you end up with blocking locks in tempdb similar to those described above. The prescribed workarou...more >>

I have 3 tables that store all the data for my application. I wanted the database structure to allow the user to define the "columns" rather than hardcode them in the database design. This allows the user to define menus, categories, and fields for this "content manager" type app. The major...more >>

Hi, I have a table having the values ID Nam age 1 aaa 17 2 abc 18 3 acb 19 4 bbb 15 5 bbc 20 6 ccc 28 I want to list all the top entries having the highest age Like 3 acb 19 5 bbc 20 6 ...more >>

Hi, Is it possible, when using sp_executesql, to see the SQL statement with = the parameters expanded? I'm losing years of life trying to get the following dynamic SQL = statement to work: DECLARE @sSQL NVARCHAR(1000) DECLARE @PropStreet1 VARCHAR(50) SET @PropStreet1 =3D '...more >>

Hi, How do I rename a database without changing the users permissions? I have tried ALTER DATABASE old_db_name MODIFY NAME = new_db_name and EXEC sp_renamedb 'old_db_name','new_db_name' and both give me: Server: Msg 5030, Level 16, State 2, Line 1 The database could not be exclu...more >>

Help please! How do you retreive a query & sort the results in a random order? Many Thanks Steve ...more >>

Given the following two tables: Parent Child ---------- --------- ParentID ParentID ChildID how can I retrieve the Child row which has the highest value of ChildID within any single Parent row? The following does not work. SELECT * FROM Parent p I...more >>

I get the following error when I run a script in our server that we use to review code. I am now getting the same error with different querries on different databases on the same server. Both servers are running SQL 2000. We just recently upgraded our OS to Win 2003, and reinstalled SQL 2000. ...more >>

I have a table in SQL Server 2000 which contains about 5 millions rows and keeps growing at the rate of 500,000 rows per month. This is a live production table into which data is inserted/updated contantly. This table contains a few indexes and keys. The activity on this table is extremely hig...more >>

Two things I could use help with. First one is I know there is probbaly a way better method to do this and I would love someone to show me what it is having just begun in the SQL trans language. The 2nd is, I am curious why, even though its clearly not the best method, why it doesn't work and ret...more >>

Hi, I have SQL Server 7 (SP6) installed on an NT4 (SP6a) and I have a problem with permissions - I set permissions on tables for a user (that is an NT User itself) - both using the enterprise manager and using SQL. Problem is that it doesn't matter - I can still perform INSERT/UPDATE/DELETE...more >>

Hi,=20 is there a method of getting table current size? TIA!!...more >>

I have a table that has a unique ID assigned by a random number in an INSERT trigger. This trigger was created from an Access upload of a table with a random autonumber (replicated table). I am using the code below to add a new record to the table but it does not return the new ID. Besides ...more >>

Hi all, I asked a question earlier about speeding up a union all statment like below SELECT code, description, clientDescription, band, AVG(rental) AS value, COUNT(tableTemp.surveyID) AS [count], CASE clientID WHEN @clientID THEN 1 ELSE 0 ...more >>

What is the correct (or best way) to Insert Into an Image data field. I have an app that displays records containing Equipment Pictures. I am able to display the record and display the picture. I want to allow the user to add an new record and also insert a new picture associated with the r...more >>

I have created the following UDF: CREATE FUNCTION dbo.udf_ConvertBC (@ValToConvert varchar(255)) RETURNS varchar(255) AS BEGIN DECLARE @Object int, @return varchar(255), @MethodToCall varchar(255) SET @MethodToCall = 'c128setbhumanreadable(' + @ValToConvert + ')' EXEC sp_OA...more >>

I want to export data from my tables by generating insert statements, including data of type image. To avoid having to use textcopy.exe or textptr, I want to have the image data part of the insert-statements by converting the binary image data to hexadecimal strings. Also, the image data is larg...more >>

Hi, I have created the following 5 stored procedures which return rows based on the user input... Now what i need to know is that is there a way of ....rather than writing 5 stored procedures....I can merge all these into a single stored procedure.... so that when the user doesnt enter a...more >>

How would I get the number of characters on a text column? select len(mytextcol) from mytable -- fails tia, chris...more >>

Hi guys... This SP works: /*******************************************************/ Create Procedure tmpMF1 as Create Table ##fun (line varchar(1000)); insert into ##fun (line) values ('Hello'); exec Master..xp_cmdshell 'bcp "select line from ##fun" queryout "c:\fun.txt" -c -S"...more >>

Can anyone help me. I have a basic DTS package exporting data and all I want to do it click on a button in Vb and trigger this package to run. Should I be looking at the command object, or am I way off track here? I'd really appreciate any help anyone can give me. Thanks Dawn...more >>

I am trying to use XP_sendmail and get the following error: ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER. Can anyone help ? Thanks in advance, Kyle...more >>

Hi All, I have a little bit of code that looks something like this SELECT code, [description], clientDescription, band, AVG(rental) AS value, COUNT(tableTemp.surveyID) AS [count], CASE clientID WHEN @clientID THEN 1 ELSE 0 END AS color ...more >>