In my stored procedure I need to put data I get from dynamically built SELECT query into particular temporary table. i.e. EXEC ('SELECT 1 AS [number] INTO #t') SELECT * FROM #t Of course this doesn't work because of scope of [#t] Unfortunatelly I don't know exact structure of [#t] in advance, ...more >>

ALTER procedure [dbo].[fetch_customer] @pageno integer, @pagelength integer, @sorting varchar(32), @keyword varchar(100) as set nocount on declare @startrow integer if @pageno<0 begin declare @ttl integer select @ttl=count(*) from customer set @startrow=@ttl/@pagelength*@pagelength...more >>

Hi, I'm trying to develope a script which would be running against the server every 10 minutes, and whose purpose would be to check availability of all databases on the server. I am using cursor and dynamic sql for that (see below). At the same time, I want to return custom error message and...more >>

Hi, I have a simple table with 4 coloums. it has 3,00,000 records. there is a primary key. thats it. a simple count query is taking 3 minutes. we are using sql 2000 standard edition. can anyone assist?? -- Regards Ginny ...more >>

All, Is there anybody familiar enough with the datepart calculations to show me the ropes on how to replicate this functionality in T-SQL? I believe I've got the FirstDayofWeek part figured out, just not the FirstWeekOfYear calculation. Regards, tristan ...more >>

Hi all, I am not sure how to write the query for the following scenario: I have a table with the following structure (there is a primary key set across all columns) - Column1 (RelationshipId) Column2 (OrganisationName) Column3 (OrganisationRole) An example of the data I would have in the...more >>

Hello, I am attempting to create a SQL Statement that only returns certain rows to data and am having a very difficult time getting it to work. This is what I am trying to do: I have a table full of data: ID CustomerID, CustomerFirst, CustomerLast, CustomerBalance, TransDate 1 ...more >>

After extensive testing, with various sizes of tables (the report below = concerns a table of 10 million rows), I have concluded that my earlier = assertion that using [BETWEEN] seemed to be more efficient than using [ = >=3D AND <=3D ] was not correct -as many of you gently countered. There = w...more >>

hello all, i have a select problem with 2 tables using COUNT in sql server: QUESTION table: ====================================== id question_type question text -------------------------------------- 1 1 question1a_text 2 1 question1b_text 3 ...more >>

Hi, Is there a way to put the Select @LastLogin = [LastLogin] in the Exists funtion rather than use the get all * ? The reason I ask is feel im filtering for the same informaation more than once. Ive included the SP what works at the moment but just wondered if this is the best way to chec...more >>

Hi, I'm trying to write a stored procedure that would handle dateparts correctly based upon table configurations. However, the dateparts (dw, wk) I'm interested in are sensitive to the SET DATEFIRST option. To test whether or not this is even feasible, I set out to write a test stored pro...more >>

Hi, I have the follow data in a table COMP CUST PROD DELIV QTYDEL QTYRET TEST1 123 ABC 01012006 2 0 TEST1 123 ABC 01012006 NULL 1 TEST2 456 ...more >>

Hi, all I have a SQL command like this, SELECT * FROM #temp WHERE listname NOT IN (SELECT stockcode +'-'+loc from tbl_stocklist) but when i execute this command in SQL analyzer, it turned out the following err: err info: Cannot resolve collation conflict...more >>

I have several scripts like: CreateTable_tblEmployee.sql AlterTable_tblEmployee.sql CreateProcedure_stpSelectEmployee.sql ....that contain what you think they contain. Is there a way I can write a single script or some sort of batch job that executes the scripts in an order I specify? ...more >>

Hi, I am new to SQL and am trying to run a query with two parameters (first name and last name). I am getting the qry to filter out data by first name and last name but when I leave first name and last name blank, it doesn't show all the records in the table. Some of the customer records do ...more >>

if I have 2 tables, say TABLEA and TABLEB with the same schema for example, TABLEA and TBALEB with only 1 column "NAME", TABLEA contains 5 rows: NAME -------- John Peter Mark Tom Ben TABLEB contains 4 rows: NAME -------- May Jess Ada Margaret Can I using one T-SQL statement to...more >>

I am trying to provide my users a history of name changes from a security table. Basically, they want to know each time the users name changed and the date. I have a history table with the data, but am having trouble selecting the data correctly. I am using the following in a stored procedure...more >>

Hi All, I use DAO VB command like : s = "BACKUP DATABASE cas2 " & _ "TO DISK = 'h:\testdb\cas2.bak' WITH INIT, STATS = 5" cn.Execute s, dbExecDirect to backup my database. Now I want to complete my command so that it can show a progress meter when it runs. I want to captur...more >>

Hello - I have the following stored procedure: CREATE Procedure spCrDetailsEntire @FromDate datetime, @ToDate datetime As Select c.Agent, c.CallerName, c.Phone, c.CallDate, c.LoanType, c.NumbApps, c.Comment, d.DeclarationID, d.DeclarationNo, d.Declaration, s.State, ct.CallTypeID...more >>

------------------------------------------------------------------- Exp# TableDef ColsUpdated columns_updated() binary value ------------------------------------------------------------------- 1. 17 fields c14 & c15 24576 0110 0000 0000 0000 2. 16 fields c14 & c...more >>

Hi, In one of my SQL 2000 tables I have a field which I have set to decimal, in my update SP I have set the parameter as decimal. However when I pass in e.g. 1.15 from my VB front end, it is putting 1 in the table. Why is this? Is there a bug? Appreciate any help on this as someone els...more >>

If I have Calendar Control on Asp.net page and I have a table for Fiscal years on sql server then, How can I check the dates from table to Calendar Control,so that I can format the Calendar control cells according to my need. I want to change the color of Dates according to fiscal month. i.e. A...more >>

I am extracting data from a table that has a date format with the time in it. I would like to extract the date in mm/dd/yyyy format to place in a Crystal Reports to do daily calculations on information. I can not change the table as I don't have the rights to do that, so I am trying to manipul...more >>

Hi I have a table which has the following fields: ID (Int, IDENTITY) SerialNumber (?) OrderID (Integer) The SerialNumber is a 15 digit number, and each Order may contain 10,000+ rows. The table will be populated through ADO.Net, the data being appended from Excel spreadsheets that the use...more >>

Amish Shah wrote to me: "This is error comes because your primray filegroup of Tempdb database is full. Move tempdb to some other location where enough space or If you have put size limit in tempdb then increase it. " Thank you Amish! Your comments helped! I fixed this problem but now have ...more >>

I have MAIN_TABLE with four fields: [ID], [full_name], [Dept], [Tel]. Fields [ID], [full_name] have data. [Dept] and [Tel] are empty. My pseudocode would be: Update MAIN_TABLE set [Dept] = (select DEPT_NAME from Table1 where MAIN_TABLE.full_name = Table1.full_name), set [Tel] = (select TEL ...more >>

i have a script where i need to show all Sells as negative values, how can i go about doing this? ...more >>

Hi, I'm having a problem with a SQL query that uses a bunch of "case when" statements. I am fairly new at this, so bear with me. We are trying to determine how many people have the different versions of antivirus software on their machines. In the instance where they have none of the thre...more >>

I've got a table with 10 million+ rows, with only one index on an INT column, about 10% selective. As part of some redesign, we're dropping that index and creating a new clustered index across two columns, including the original INT column and a VARCHAR(12) column; still not 100% unique but g...more >>

We have a problem with a query running finn on our development server, but not on the production server. The below query highlights the problem. On one server, the date must be DD/MM/YYYY and on the other server it must be YYYY/MM/DD Does anybody know what setting controls how SQL Server us...more >>

Ok I have a minor sistuation here where I am gathering information from all of my 13 production servers to see how much space is being taken up by the databases and there log files. All of my servers except for one can run this code succesfully with no problems to return the results. ----...more >>

I am writing a query that shows all activity from two of our clients with any partner they may have done business with. select partner name, (select sum(partnercharge) from bills as b2 where b2.partnerid = bills.partnerid and client = 1), (select sum(partnercharge) from bills as b3 where ...more >>

Hello All, I am stuck with this issue for 2 days now and I am out of ideas.......I need to Merge Multiple Records in a Table and put them into a Single Record. For Example:- ID Name Age SSN Comments --- --------- ------- -------- ---------...more >>

Hi, Lately, I have been getting this problem with a client. The query returns around 6 thousand records, there is one user only and it's a select statement with a temporary table in the join. The server has a sp4 installed. It's a 2003 server. CREATE TABLE #Temp (Temp_Id INT, ...more >>

Hi all, I have the following table definition: Table: Documents - DocumentID - ParentID - Name - Position What i need to acheive is as follows: SELECT DocumentID, ParentID, Name, Position FROM Documents WHERE ParentID = 0 ORDER BY Position Then iterate thro...more >>

Hi all, In SQL 2000. Suppose I have a stored procedure where @SQLWhere is a varchar(100) parameter which contains the WHERE Clause which will be used with a SELECT Clause(which is in the store procedure). How can I put all of this together so that the whole query (SELECT+WHERE) will work co...more >>

Does anyone know what the advantages or disadvantages are to running an if-statement inside a stored procedure that then determines which stored procedur to run and then return results? Thanks ...more >>

Hi I have a table with a varchar column that is being replaced with a smallint adn a new lookup table. I have created and populated the lookup table, how do I update the main table with the required IDs ? Can this be done using a set based update or do I need to loop through the records, lookup...more >>

Hi I have a table in following format, where ID is the primary key of the table. ID ProductID ArticleStatus Article 1 A p abc 2 A p xyz 3 A p abc 4 A q abc 5 A q xyz 6 B p abc 7 B q abc 8 C p qwe 9 D r ewt 10 E s jhj I want the query tht giv...more >>

hi all, I got the following error in a stored procedure on a SQL 2000 : "An exception occurred in the TSQL" "subsystem." The stored procedure was executed as a step in a job; It copies data to a linked server, which is an ODBC driver for a Pervasive database. When I tried to restart the job...more >>

Hi guys Okay, I've got a table of agreements which is basically as follows... AAID ProjectID ContactID FileName Created --------------------------------------------------------------------------- 1 200 341 P200-AA1.doc 2006-06-29 2 ...more >>

Hi to all, I have a table called People and another contract Description of people is : - ID - Name Description of Contract is: - Number - Quantity - PeopleID I would like to get this: a= sum of quantity with the same number of contract b= a + the sum of the quantity forea...more >>

I am having trouble with the following Query. I have included the relevant small piece of DDL. I need to get the most recent windscreen cover flag (Y/N) for each policy, selected by the policy_code (POLICY_CODE), transaction type (TRANS_TYPE) and using the maximum effective date (EFFECTIVE_DT)...more >>

Hi, The sqlcmd does not always write the information to the log file ? Could there be any specific reason for this sqlcmd -S SRV001 -U JP -P SHENOY -i "c:\test.sql" -o "c:\test.log" -v data_file="c:\test.idl" format_file="c:\test.xml" dbname="MYDB" -o option indicates the log f...more >>