Hi all, I have written a Stored Procedure With lot of statements like Select @variableName= field from table Name. And In between Iam executing a Select * from ## temp table. And there are lots Of RAISERROR statements in the Stored Procedure This Stored Procedure is called Via ADO.NET, ...more >>

SQL Server doesn't appear to have the concept of a global or public constant. For instance, if LanguageID 1 equals English or LanguageID 2 equals Spanish, you'd typically have to hard code 1 or 2 in any procedure that checks this permission first. I've recently started using User-Defined Fun...more >>

I would like to rank the following. At the moment I am retrieving the following SELECT Users.Alias, SUM(Field.Points) AS Points FROM Field INNER JOIN Picks ON Field.ItemID = Picks.ItemID INNER JOIN Users ON Picks.UserID = Users.UserID GROUP BY Users.Alias ORDER BY SUM(Field.Points) DESC ...more >>

...more >>

How can i modify the following code to touch every row created by the table join? Right now it's only updating the first row. DECLARE @ptrval binary(16) DECLARE @txt as varchar(110) SELECT @ptrval = TEXTPTR(ntextfield) FROM tableA INNER JOIN tableB ON tableA.ID= tableB.ID SET @txt='stuff ...more >>

Is there a way to subtract a specific quantity from one field, (column & row), in one specific table from a quantity in a specific field, (column & row), from another table, like subtract "units on order" from one specific table from "units in stock" from another table? Thanks...more >>

Okay. So, I have this query, that counts phone numbers, and then returns me the count. This query is run from my asp.net app. For some reason though, the connection kept timing out. (and throwing an error...) It was driving me nuts. So then I converted it to a stored procedure. It didn't reall...more >>

Hi, I am VERY new to SQL and I do not know if I am word this question correctly, so forgive me. I would like to create a new table that does calculations on some of the same fields. • IPA number (from the IPA_Num field) • Total number of Rx's (sum of rx field ) • Total Paid( su...more >>

Hi - I see from a lot of the docs that you can use a case statement in the where clause. I'd like to do the following: The value in another table I query will dictate which region I want to query. Will something like I've listed here work? get a value from a table @v1 = x select c1, c2...more >>

Hi, I am developing an add on to a db application where I need to install a couple of tables to an existing db. I am curious what others use for installation. Do most developers use SQL scripts excuted by ossql command in a batch file? Thanks ...more >>

The outcome of the following 2 simple queries ---------------------------------------- PRINT 15 & 75 PRINT 15 | 75 ---------------------------------------- are 11 & 79 respectively. Can someone explain how does SQL Server compute these values? I went through the topic 'Bitwise Operators' i...more >>

Hello, I know this question is very basic but I'd like to hear it from experts :) Is it ok to use NULL values in database or should use '' when applicable. Obviously sometimes you have to use NULL (datetime). Thanks, Vlado ...more >>

Hello Everyone, I was wondering what the pros and cons are on using Subqueries in SQL SERVER? -- J. D. ...more >>

I have a problem that up to now I've been to more or less avoid or duck until now. I have a dataset that is drawn from a number of tables, including several of the same tables aliased. My problem lies in that for a particular record, because it appears in other tables more than once I got many...more >>

I just found this contact in our database. CLMRKey = 17716589 CMRKey = 2364205 FullName = *TYPE IN CONTACT NAME* FirstName = TYPE MiddleName = IN CONTACT LastName = NAME ContactKey = NULL When a programmer does stuff like prepopulate a text box with a user message like "Type Something H...more >>

Hi, I have a transaction table that basically has the following fields RecId, PeriodId, Quantity (a single RecId can have multiple records, i.e. quantities in multiple periods) I need to convert an entire table of these records to one that looks like this... RedId , P1Qty, P2Qty, ...more >>

In an ADP, I typically keep all of my data gathering in Stored Procs on the server as opposed to doing the Selects from the code behind the forms. Keeps all of the data from coming across the wire. If I were to hook a subform to a main form using the link child master method, would this je...more >>

We're in the process of scrubbing all of our customer data in preperation for an ERP system implementation. I need to write a UDF that will take a varchar string as input and return a camel case string but I wanted to check to see if this has already been done somehwere else first, I already s...more >>

Ok, I have two records from table planning. planner Item qty Date PMJ 11174/5G-1 8 8/12/2005 PMJ 11174/5G-1 3 11/10/2005 I Just want to select the first record without doing a date dependent query. In other words, the quer...more >>

Hi, I have one stored procedure with multiple selects, like this: SELECT @NumUsers = COUNT(*) ... SELECT @NumPages = COUNT(*) ... Now it returns nothing, I suspected the count values. I tried it with return... but then I only get one value. How can I get the values as a table? With NumUs...more >>

I have two image fields in one table and I want to update one of the image fields with the content of the second image field. I am thinking of doing something like this, but it is not working: update actinfo set Image1 = (select image2 from actinfo where [ID] = 'x123') where [ID] = '137' ...more >>

Hello All, My application takes data readings every few seconds and stores a couple of data values for piece of equipment in a table for historic trending. The table is simlar to: CREATE TABLE [DataTest] ( [DataID] [bigint] IDENTITY (1, 1) NOT NULL , [EquipmentID] [int] NOT...more >>

Hello, I have the following statements: declare @invoice_date smalldatetime declare @due_date smalldatetime declare @duration int declare calculate_date cursor forward_only read_only for select data, due_date from data1 open calculate_date fetch next from calculate_date into @invoice...more >>

Is MS SQL 2005 Express (or server) compatible with Visual Basic 6.0 ? Can I use new SQL 2005 Express with Visual Basic 6.0 ? Your answer is greatly appreciated!! ...more >>

Ok I have been scanning the new group archives and know this subject has be breached before but I can not find a resolution so bare with me please. First I don't want to install the MSDE, all my clients have the fill version of SQL installed and normally on a server not located on the same co...more >>

I need to put a constraint on a table but am unsure how to proceed. Consider the following fields: AcctNo - varchar(10), indexed, duplicates OK Status - varchar(2) The logical rules I need to enforce are such that if any records exist with duplicate values in the AcctNo field, only one ...more >>

Simple question, what is that I should use to delete data from one table and keep the table's definition? DROP TABLE deletes everything and I need to maintain the table's definition. Thanks a lot. -- TS...more >>

I would like to define a unique constraint over a series of columnsm, one of which is a column of type BIT. It seems that this cannot be done. Any suggestions on how to otherwise best enforce this unique constraint through the database. Is a TRIGGER my best option? Should I change the d...more >>

I have a "best way" question on Stored Procedures. In my Asp.Net, I try to use only SP to get update my data. But in the case of getting data, the problem is the number of SP's that I have to set up for each instance. For example, if I have an Employee table with 30 or 40 fields, would it...more >>

I have a table in which I converted a 5 digit ZIP code field from numeric to text. This leaves some fields as 4 character value (no leading zero). If there a way to compose a query to correct this? In looking through the help I see the LEN function but from what I read, it won't work in a quer...more >>

All, Without using identity and assume i don't care if i have holes in my numbers, is this the best way to get and set the next number? update NUMID set @x = NUMID.CS_ID_NUM = NUMID.CS_ID_NUM + 1 from numid (nolock) Steve ...more >>

Hi, I have a sproc with 5 params that takes about 40 seconds to return. But when I Create a Temp table and do a Insert Into #temp Exec sproc param1, param2, param3, param4, param5 it never returns... any ideas? Thanks, Bill ...more >>

hello What query shoul I send to SQL serwer ( in transact SQL language ) to check does some database exist on serwer ? It similar to problem "does some table exist in database" - resolve to it is query: use db_silnik IF EXISTS (SELECT * FROM prad) PRINT 'table exist' but what is the ...more >>

I wrote the following query for finding duplicates in a small table using 3 fields which runs fine for a small table, but the actual table has hundreds of thousands of rows and this query was way to slow. So what I did was to break the query down and insert a reduced subset of data from the m...more >>

Hello: how do I access an error code when SELECT @RESULTS EXEC('DBCC DBREINDEX('''+@NAME+''') ') fails because the database..table does not exists ? @RESULTS comes back with nothing but I get Server: Msg 2501, Level 16, State 1, Line 1 Could not find a table or object named 'Internet_Form...more >>

Hello I need some help to make a query for these 2 tables: TblEmployee ID EmployeeName TblEmployeeChild ID SubjectID Mark I want to select Employeename, and count of tblEmployeeChild.ID where subjectID > 5 But I also want to list all the employeeName and display count as 0...more >>

Hi, I am looking for a way to simulate the VB Format function using T-SQL in stored procedure. There is some bad design issues wiith an existing application that concatenates a string and a number and makes sure that if there are any remaing characters in the column left unfilled that they ...more >>

I am using SQL Server 2000 SP 3a enterprise. We do a nightly backup to a network location and then in the middle of the night the files are written to tape from the network. We have verified our backup plan will work by choosing a backup and restoring it to the database. We have done this m...more >>

I have two tables that have a one to one relationship between them. I must be able to keep the keys in sync. I would need to handle this in a stored procedure. Does anyone have any suggestion on how I can do this? Table A - CD_CORP_PK and CD_INC_FK Table B - CD_INC_PK and CD_CORP_FK ...more >>

Hi I have a table with three lengths of name in it (amongst other fields) - LongNm, MedNm and ShortNm. Sometimes ShortNm is null, so at times I want to select a list that is alphabetical using conditional versions of the names: So if ShortNm is null I want to select MedNm AS AnylengthNm I...more >>

Hi I have googled but could not find the answer how can I do the following. exec my_sp < foo.csv I.e feed my_sp with the contents of a external file? my_sp expects one parameter @param1 the csv file or tsv or whatever looks like this: 1 2 3 4 5 6 7 8 9 10 ....... is this p...more >>

Hi, I am trying to build a view in SQL server. I have a function in Access which looks like this: Breach: IIf(DateDiff("n",[PP_ARRIVAL_DATE],[PP_DISCHARGE_DATE])>240,"Breach","Non Breach") >From reading it is clear that the IIF statement is not available in SQLServer what do i need to u...more >>

I ve got the following TSQL script: Declare firstQuery Cursor for SELECT [id] FROM TableName for Read Only; Declare @myid int; Open firstQuery; Fetch Next from firstQuery Into @myid; WHILE (@@fetch_status <> -1) BEGIN EXEC myStoredProc @param1 = @myid; Fetc...more >>

CREATE PROCEDURE DT @TEMP_TABLE_NAME SYSNAME AS DECLARE @STATEMENT VARCHAR(8000) SET @STATEMENT ='DROP TABLE '+@TEMP_TABLE_NAME IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @TEMP_TABLE_NAME) BEGIN EXEC(@STATEMENT) END SELECT * INTO #AA FROM a_table DT '#AA' SELECT * FROM #...more >>

I'm having trouble with an insert statement. It started in VB Script, but then I was able to duplicate it in SQL Query Analyzer. I've been banging my head against this long enough to have lost perspective, hahaha. Can anyone help out? SQL Statement as written to the ODBC interface and run i...more >>

I'm migrating denormalized database to a normalized one. I'm planning not to use DTS but do it this way 1. Use a front end to read tables to dataset 2. insert the dataset to a database view 3. the view has an instead-of-insert triggers that shall handle the transforms and other logical...more >>

Hi all, I am fairly new to SQL Server and I have a question: I got a rather complex Stored Procedure (dbo.asb) which puts out a few lines with different columns. Now I want to work with those columns within a second procedure, like: declare @rc int declare Fields Cursor FOR SELECT * FROM ...more >>

Hi. The below Stored Procedure fail (without any comment) when I call it. Could any of you pro's please correct me CREATE PROCEDURE sp_UpdateMyTable (@cliid Int,@field varchar(50), @fieldvalue varchar(50)) AS UPDATE MyTable SET @field = @fieldvalue, lastchanged = GetDate() WHERE id = @cl...more >>

I have a field in a database which is a delimited string. i.e. accountnum.message.date I want to use the first portion of that string (accountnum) to return from another table the matching value, in this case the accountname. I can do this using VB manually, i.e. one record at a time, but ...more >>

Hi gurus. I want to implement a full-text search support for texts of my native language in MS SQL Server 2005. But my language (Ukrainian) is not supported. So i would like to implement a module for this language by myself. Unfortunately i've failed to find what API i have to implement. ...more >>

What is the maximum number of tables that SQL Server 2000 can cope with? ...more >>