hi i have two tables items student code name 1 null book 2 null book 3 null pen .. .. going anothr table item_type name code book code1 pen code2 now i want to update table item colmn code is ...more >>

Is it possible to create a cursor with some input parameter on which the cursor query can be based, if so then what is the syntax of using that? -- Ejaz ul Haq...more >>

Is there any performance problem in using OPENXML in stored procedures? Thanx in Advance...more >>

Hello, How could I capture the max of an identity column and pad it with zeroes? I tried this but it doesn't return just 1 number: Select replicate('0', 10 - datalength(max(P.PurchaseOrderId))) + cast(max(P.PurchaseOrderID) as varchar) From PurchaseOrder P group by P.PurchaseOrderId Or ...more >>

I am trying to create a query as below select fname, date1, date2, date3 from mytable where fname = 'me' order by [max of date1, date2, date3] but I (clearly) don't know how to acheive the ordering. Any help is appreciated Thanks! ...more >>

Hi if i have a table like PartNumber WalMart9 Target8 HomeDepot7 123 $10 $12 $11 CDE $5 $4.5 $5.2 is there anyway I could create a colmn call...more >>

Hello! There are some recursive procedures in my app. Normally they have a maximum nesting level of 4-5. ADO uses FMTONLY ON to get metadata from sql server. Recursive stored procedures sometimes will fail with "Maximum stored procedure, function, trigger, or view nesting level exceeded (l...more >>

I want to drop all users other than the dbo from a db. How can I do so with TSQL ? Thanks ...more >>

Table A: custID int locID int fKey int Table B: custID fKey on table B, 1 custID can have multiple associated fKey records 1 A 1 B 1 C 2 H 2 K Now I want to insert table B into table A and also increase locID by 1 for every fKEy record associated w...more >>

I've been working on a query to combine two tables and produce a resulting recordset. I can't seem to get the correct query. Could someone help me? This is where I am now: DECLARE @id INT SET @id = 122 SELECT Table2.question, Table2.link, Table2.url FROM Table1 CROSS JOIN ( SELECT 1 UN...more >>

I have a table with about 50k recs that have different variations of the customer name. i.e.: A & A RENTS INC A & A RENTS, INC A & A RENTS, INC. the variations are inconsistent and the possibilities seem substantial. Has anyone put together an algorithm to consolidate Records in the a...more >>

Hello how can I describe the structure of a table using t-sql ? I tried sp_helptext and it is supposed to do only trigers and views! how can I list the constraints as well ? thanks...more >>

I am trying to select records based on the value in a column. I have one column called pm and it has either a 'P' or an 'M' in it. If the column has a 'P' then I want to select statement using one set of calculations and if it has an 'M' then I want to use a second set of calcultions. Here ...more >>

Hi, I desperatly need a good SQL book to learn how to write Advance Query statements. I can do the simple JOINS(inner, left, right). I want to learn how to write advance query statements like the ones experts post in this newsgroup. For example the query statement below returns User's nam...more >>

Hi Freinds, SQL 2000 I have an transactional replication. I have to update table structures. I know I can reinitialize the whole subscription to have the update on subscriber. is there any way to initialize only specific alticles to regenerated on next snapshot? Thanks, Pat ...more >>

Hi, Can anyone help me please? DECLARE @sCol varchar(10) DECLARE @iTylacode int DECLARE @sMarca varchar(50) DECLARE @SQL nvarchar(4000) SELECT @sCol = 'M6' SELECT @iTylacode = 34285 SELECT @sMarca = 'BMW' select @sql='SELECT ' +@scol select @sql= @sql + ' FROM MQS_CarPlus where TYLACO...more >>

Hi, can anyone help me please ? DECLARE @sCol varchar(10) DECLARE @iTylacode int DECLARE @sMarca varchar(50) SELECT @sCol = 'M6' SELECT @iTylacode = 34285 SELECT @sMarca = 'BMW' select @sql='SELECT ' +@scol select @sql= @sql + ' FROM MQS_CarPlus where TYLACODE= ' + convert(varchar,@iTy...more >>

Hi, I have the following Query SELECT INVOICE_ROW.INVOICE_ROW_ID, INVOICE_ROW.PRODUCT_NAME FROM INVOICE (NOLOCK), INVOICE_ROW (NOLOCK) WHERE INVOICE.CUSTOMER_ID = 515025 AND INVOICE.INVOICE_SEQ_ID = INVOICE_ROW.INVOICE_SEQ_ID AND INVOICE_ROW.PRODUCT_NAME LIKE 'VERBATIM%' Now, this tak...more >>

I have some data that I want to select with an ORDER BY statement and be = able to have it sorted in a hierrarchical order. There will be essentially 1) and ID, and 2) a ParentID. I would like to have the data returned sorted such that any row with a = ParentID =3D to an ID is under that ID ...more >>

HI - Need help please: In SQL Server 2000; columns set to allow NULL, but will not accept NULL either through enterprise manager or odbc. When I try to change to NOT NULL, the following msg appears when saving the table in enterprise manager: 'LOOK_RID' table - Unable to modify table. ...more >>

Hi All, I have the Below Problem: CREATE TABLE INVMiscCharges( InvoiceMiscID INT IDENTITY (1,1), ProgramID INT, InvoiceID INT, ServiceID INT, ItemDesc VARCHAR(35), Quantity FLOAT, Amount FLOAT, CONSTRAINT PK_INVOICEMISCID PRIMARY KEY(INVOICEMISCID) ) GO INSERT INTO IN...more >>

I'd like to create a UDF similar to the Access IIF function. It will be used as: SELECT NullIIF( col1, col2, col3) from aTable Such that if col1 IS NOT NULL then return col2, else return col3. I know I can use the CASE statement, but I hate all that typing - I need to use this function s...more >>

Any help would be appreciated. I have a single table uploaded to an sql database. the table name is INDEXDB1 the fields are Vendors, Invoices, Checks At the present time each row has vendor, invoices, check for every transaction. there has to be a better way then to enter the same data e...more >>

Is there a built-in SQL-function that calculates the end of a given month? ...more >>

Hi all, I have a column that has information with a path seperator in it: xyz\fes xyz\fewji I am trying to update the table with a column called Account so that only what comes after the '\' exists. I wrote this query which does not work...any help? SELECT SUBSTRING(Account,SELEC...more >>

How can it be done? I cannot found a reference to it anywhere. ...more >>

Hi, it is possible to alter a column involved in a pk using sql-dmo? and if so, should i drop the pk constraint, modify the column and then recreate the pk using new column? the same question about modifying pk's: in order to modify a pk - i want to add/remove a column - must i drop the pk fir...more >>

What are some free tools to manage ms sql. Where can I find them thanks...more >>

I know that when I kill transaction from Sql analyzer, the table remains lock. what happened to transaction when client computer shutdown, or system crash, and what happened when I force the client app to ended by taskmanager? ...more >>

I have a statement that I need to refer to a text field for display purposes (Compensation, in my example). SELECT ReferenceCode,DatePosted = Convert(varchar,min(DatePosted),101),Compensation from Position p where ReferenceCode = '110052-AC' group by ReferenceCode,Compensation Server: Ms...more >>

Hi, Am trying to create a sproc that will quite simply insert new records into a table. Done that bit with no problems and can call it from my web app by passing the relevant parameters. However, what I would like to do is if one particular field doesn't recieve a value, i.e. if Null is p...more >>

The connection string below returns the error 'not associated with a trusted SQL server connection'. The Windows 2000 server were the SQL instance resides is in Mixed authentication mode and I have added the user login. strConn = "workstation id=http://myDomainName.com/ServerName" ...more >>

hi i need to get a return value (to handle error) of a SQL Server store procedure in VB using ado, (connection.execute), how can i do?? thanks Carlo ...more >>

3 tables: Address Person Mailings ------- --------- --------- AddressID PK PersonID PK PersonID FK AddressID FK No action on update or delete for both FKs There are 3 records in Person each with a value of 185 in AddressID and PersonIDs of 1...more >>

I have been looking for a discussion on this but havn't seen one so here goes. Suppose I have a Table with a column for Status like Order.Status where Status is one of a fixed set of values i.e. {Status.Open, Status.Closed, Status.Cancelled} and these status are not user configurable. Norma...more >>

I have my own data but I have been testing this with the Northwind.dbo.[Order Details] data. Basically the first few rows are (ordered by orderid, sic) orderid productid ----------- ----------- 10248 11 10248 42 10248 72 10249 14 10249 51 10250 ...more >>

I am trying to call a UDF as follows: SELECT A.AgreementNo, A.SponsorName, isnull((Select distinct 'Y' from dbo.fnCenterSLP(A.AgreementNo,'All', 166) Where Sponsor = A.AgreementNo AND (Status = 'Approved' OR Status = 'Suspended')),'_') as NSLP, FROM tblA...more >>

In our VB application, we currently stored a value into an Image field using AppendChunk with the following code (variables declarations etc not shown):- With recMisc .Open "TestTable", gDBConn.cn, adOpenKeyset, adLockOptimistic .AddNew !FileLength...more >>

I have two fields in a database, StartVisit, and EndVisit. Datediff(mi, Startvisit, EndVisit) as duration It is stored as numeric. How can I convert Duration value into readable times in SQL? Any help would be appreciated. ...more >>

Hi. At first let me present the problem: T(a int, b int, c bit) I would like to see to it that (a,b) is unique where c = 1. The easiest way to accomplish this would be creating a view like this: create view V as select a,b from T where c = 1 And then putting a unique constraint on (a,b...more >>

I am posting a rather general question here regarding "Replication". I have a client (actually many clients) where it would be nice if they can replicate their live database to my server in the event of power outage, server troubles, general backup, etc. I have played around with it but not ...more >>

I just saw a web application that apparently ran a query on a database and then gave the user the results back in a pdf document. Does anyone have experience doing something like this? I'd like to know if this is something that SQL can handle directly or if there's something additional I ha...more >>

I have a simple SELECT query that uses an IIF statement to return one of two values. I keep getting an "Error 170, Line 9, Incorrect Syntax near "=". This is the code: iif(isECN=1, addressline1, ssn) as Type Field isECN is a True/False field that stores values of 0 and 1 What am I doin...more >>

im trying to update a table from a vb application. The table contains two fields Licence & Companyname. Licence number is unique how can i do an insert if the licence doesnt exist and an update if it does?...more >>

Hi how can extract the userid from an email...the email address are in the format of userid@ucmerced.edu I want to extract everything until the character "@". Thanks in advance....more >>

hello i want to get all records from items table such as name,id_item, and others and i have a function in database that returns dominant netto_price and brutto_price because there are few tables with individual prices for each contractor. Contractor_id is a input parameter in function but...more >>

I need to run a query that gives me a count of how many records were inserted based on client filter criteria. Client has two filter units Hours and Days Hours can go from 1 to 48 and Days can go from 1 to 30 I need count(*) grouped by based on client fliter unit. If client says 4 hours ...more >>

I have an sql table that has the following fields Vendor Name ( multipule times) Vendor Invoices ( unique invoice numbers) Checks ( checks written against invoices) I need somehow to pull the data as follows Vendor Vendor invoices Checks From a single table any assistance would be gr...more >>

I have several tables in the range of 5 - 10 million and I need to create a list of all distinct lookup list values used. Does anyone have experience in finding distinct values on very large tables efficiently? The lookups we are dealing with on average has under 30 rows, however; we on...more >>

Hello, I wrote a stored procedure as this: CREATE PROCEDURE SP_CheckStatus2 @mylist varchar(1000) as select name, emailname from tbl_person where name in (@mylist) or emailname in (@mylist) GO when I try to run it like: exec sp_checkstatus2 "'jerry',"tom'" no record was re...more >>

Is there a script or routine I can use to verifiy the record counts when I backup a database and restore it on another server. Need to compare, as a check, record counts on original server databases to new location....more >>

Dear All, Based upon the following data ID Originator NewID 9 0 1 29 9 2 49 9 3 69 0 4 89 69 5 I would like to update the table so that the Originator as the NewID number i.e ID ...more >>

When we create a table using T-SQL or GUI, how to create a column to store formatted data, such as Social Security numbers or phone numbers? Thanks Victor...more >>

Hi friends! Could anyone tell me how can I receive messages of SQL Server from VB? Example, error messages or messages in a @string. Thanks!...more >>

Is there any function in T-SQL that can provide the number of any week day specified like 0-6 or 1-7 from Monday to Sunday respectively. -- Ejaz ul Haq...more >>

Hi I've been finding recently that on tables 1 mil to 5 mil records that it takes more time creating the index then time that I save using the index. Althoug it's not VLBD tables, I do apply "complex" queries to the indexed tables, groups by, joins , min , max, subselects etc. Most of ...more >>

Greetings, grateful if someone could help me on this one. I use variable @Str_MonthCol as a string to build the column name that I want to update. So the story goes like this I increment a counter from 1 up to 12 and I use a loop to update the table DNA_SalesBudgetAnalysis which contain c...more >>

Hi, I'd like to know how to identify a table whose data are lately updated in a database. More precisely, I have a database containning more than 100 tables, an application updates data relative to this database, I want to know which tables are changed relative to the application operation....more >>