Is there a way to get all indexes from a table column and then dynamicly delete the indexes in a transact sql statement. The procedure sp_helpindex deliveres all indexes of a table, not only the ones of a specified column. Thanks in advance chris ...more >>

Hi, If I have two applications want to update a table, but with different records, does it cause any racing conditions? Thanks in advance, Mark ...more >>

I need to create a proc that searches a table. However, I want to have conditional filtering on it. For example, if I have a person table, and the user types in a filter for the LastName field like 'S%' it will return all records where the LastName begins with S. However, if the user also en...more >>

Hello, I have been trying to resolve an issue when calling a COM component method via the sp_OACreate stored procedure. CREATE PROCEDURE dbo.pmpsp_Test @message as nvarchar(250) AS -- Scratch variables used in the script DECLARE @retVal INT DECLARE @comHandle INT DECLARE @errorS...more >>

Hi: I'm a newby! I have an app that requires a varchar (10000) field. The maximum "legal" size for these fields is 8000. Is there a way to increase the size of the fields - if not then I will have to make two 8000 fields and concatenate them. Or - is there another data type that will wo...more >>

Hi, Can i use a table variable to join to another table? SAMPLE: Use Northwind Declare @table table(customerid int) Insert @table values (1) Select customers.* from customers inner join @table on customers.customerid = @table.customerid the error is -- must declare variable @table... D...more >>

Is there a way to view a specific record number from a table? When I try to copy one table from one SQL Server to another I get an error that record 22392 failed on the destination server. How can I find that particular record? Note that I do not own either server. I have an ISP type account whi...more >>

Hi, How can I insert values bigger than maximum allowed (8060 byes). Suppose that my table has four fields of type varchar(8000), I need to insert rows. Thanks, Leila ...more >>

Hi, I'm optimizing an SP that must retrieve employees hierarchy in an organization. This SP is nested to retrieve the chart after a particular EmployeeID(desired root). Each employee can see reports generated by himself and employees related(beneath) to him. For example EmployeeID 50 has two em...more >>

Hello all,=20 I need some help with this query. select orderid, PanelID, Category,=20 Round(Length/12,0) as Length, Bundlename, Bundlelayer, StationID as Station, LineNumber, CompletedDT from panel Where completedDT BETWEEN '2004-09-01 00:00' AND '2004-09-30 23:59' and = stationid=3D'28' a...more >>

Hi, Our production server is a Compaq Proliant with 2 CPU. How can I determine that my queries benefit from having 2 CPU? Are parallel plans automatically created our I must configure server options? Any help would be greatly appreciated. Leila ...more >>

Thanks, guys, for your patience. I've gotten a lot of help in the last couple of days. I have Access forms that I am converting to unbound forms. I've gotten so far as to get my SQL view written to populate the form and code to populate the controls. Now do I understand correctly that I have ...more >>

Hi, I have quite a large database with 20 000 records in it. These records are beeing paged in chunks of 20 items and displayed for the client using webpages (ASP.NET). Here is the example of SQL query used for selecting records: SELECT TOP 20 Title, DocumentNo FROM Object ORDER BY ...more >>

I am not sure I understand the compilation and execution is correct : 1) The T-SQL of create stored procedure will be compiled 2) At the very first time it is called, the execution plan will be created and loaded into the buffer 3) Any subsequent call will be using that in the buffer 4) If fo...more >>

I usually use exists word. But in one example I have problem: If exists (SELECT ....) Set @result=0 else Set @result=1 I always get 1 even if the result of SELECT statement doesn't exists. If I use sintaks like this: If (SELECT ....) is null Set @result=0 else Set @re...more >>

Hi All, How I can get the Name of the primary key of a table... Nirosh. ...more >>

Can anyone tell me what some good cheap (i.e. free online) resources are for cramming knowledge of Stored Procedures and Triggers in SQL Server 2000? I do have the Online Boks (help). I also have a SQL Server 7.0 book, could anyone also tell me if 7.0 and 2000 are significantly different in this ...more >>

Version SQLserver Version 2000 SP 3.a running on WIN2003 server . Problem definition: ---------------------------- While reading a record in a ESQL/C program I always get a NOT FOUND result Even while the record exists. The record holds a number of NULL VALUES in different fields, but not...more >>

Hi, I'm trying to write a query in which an alert column from the s table is a date time type but I only works with the hour/min/sec area of this data. This query is part of a stored procedure which will be called every 60 seconds to create an entry in tblCad_trip if the s.monday_pickup_time ...more >>

Hi Need help with a query. I have two tables drop table coverage_a go create table coverage_a ( f_name char (15), l_name char (30), year char(4), month char(2), start_date datetime ) insert into coverage_a values ('joe...more >>

I want to make a backup from my SQLServer 7.0 to another computer (shared resource). But when the backup requeste me the location for the back, I can only select the local physical disk. Is possible to use \\myothercomputer\resource1 in the location for backup? Backup process will prompt me f...more >>

Hi, Trying to select rows from table "A" that has a date, "greater then" whats in the table."B" table "A" will have the exact rows that is in table "B" and many more. They are related by IDcolumn. So table "A" will have more rows with same IDcolumn but different da...more >>

Hi All, After installing a hotfix for SQL Server on my Win2K3 Server, I've been unable to debug any stored procedures running on it from my XP Pro machine running VS.NET 2003 EA. To clarify, I can step into Stored Procedures from my workstation on a database running on the server. Howev...more >>

Hi all I'm using sql server 2000 sp3a on a windows2003 server standard edition. Sql server collation was set to hebrew on installation . Windows langage is english with hebrew support. I was working fine with hebrew in sql server untill i changed the language for non unicode programms to engli...more >>

Hi I am using a database whose collation is set to SQL_Latin1_General_CP1_CI_AS. I have a following table defined in the database. ----------------------------------------------------------------- CREATE TABLE [REGSChinese] ( [ModelNum] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_A...more >>

Does anyone know how to change the owner of a DTS package? Thanks...more >>

Hi All Is it more efficient to use select MyValue from MyTable group by MyValue or Select distinct MyValue from MyTable Both have identical query plans and cost, are they really identical or is there a difference? if so what is it? TIA Reg Besseling ...more >>

Hi, Elementary I am sure but I am stumped. SELECT `Images`.`ImageID`, `Images`.`CatID`, `Images`.`ImageName`, `Images`.`ImageCaption`, `Images`.`ImageDescription`, `Images`.`DisplayOrder`, `Images`.`Submitted`, `Images`.`Display` FROM `Images` Where ...more >>

I am updating a table that has a unique auto increment primary key (and I know its name), however, I am not using that key to update the table. How can I get the identity of the row that I just updated? For example, it would be nice if I could write something like: UPDATE statusTable SE...more >>

Hi, I am very curious if I have two tables Customers and Orders of course they have a one to many relationship without using cascade delete/update function... If i want to change the customerid, how can i do that??? It doesn't matter which table I update first, it will violate the relatio...more >>

(1)Is it possible to fill the parameters of one sp with the values of another sp? if so how? Please show me an example or refer me to some reference reading material. (2)Is it possible to have an sp that first select some data from two table then insert that data into a third table? if so ...more >>

Hi!! In select query placed below, SUM(x) expression SQL Server calculates once? Thanks!!! Dima Code: SELECT .... Count = SUM(x) .... FROM xTable AS xT ...... HAVING SUM(x) > 0 ...more >>

INDEXKEY_PROPERTY ( table_ID , index_ID , key_ID , 'IsDescending') Can anyone tell me what are index_ID and key_ID parameters? Where do I get these params if I need to see an indexed column's sorting type? Thanks, Costin ...more >>

Hi I am relatively new to using stored proc in SQL Server. I have the following questions concerning modifying the procedure signature. Here is my problem description. I create a stored proc usp_LAC_Save with 3 parameters. I create programs in production using this stored proc. Users use th...more >>

SELECT * FROM TrashMessages ORDER BY date DESC; How can make it return only the five first records and not all? / Thanks, Rickard ...more >>

I created a function that returns a varchar value..however when I run it I get this error: Server: Msg 241, Level 16, State 1, Procedure CHR_appointment_recurrence_status, Line 39 Syntax error converting datetime from character string. heres the function code: CREATE FUNCTION CHR_appoi...more >>

Hi Freinds, My db is 10 GB in size, and want to get into a transactional replication The initial snapshot takes a long time to finish up. What will happen if during this initialization process, users work on pubisher db, insert, update delete record.... Thanks in advance, Pat ...more >>

Hi , I get the error ODBC error 208 (42S02) Invalid object name when I execute this Due to the multiple conditions I had to use the temp table, Depending on the conditions email is send for that group only. insert into #emp(emp,empname,group)select empid,emplyeename,groupnum from emp EX...more >>

I get this error when I am using the XP_sendMail . The query alone when I run its fine but when I Exec master.dbo.xp_sendmail @Recipients='KLLL', @Message = @EmailText, @subject='asddas', @Query='select * from #temptbl', @Attachments = @attachName, @attach_results = 'true', @width =3...more >>

I got two same database (one is backup , one is the current one) Now, I had amend some datafield name in some table (in the current one) Any utility can let me to compare the two tables structures ?? Or i need to use sp_help (list all the field and compare manually ?) Thanks From SQL server n...more >>

I have a situation where I have a SQL Server DB that is part of a 3rd party application that I am accessing for reporting purposes. I have absolutely no control over the DB so I can not modify it. The data is super-ultra normalized and has really bad field names so I decided to create anothe...more >>

Hello... By code is there anyway to do the operations backup/restore to a SQl Server DB? ...more >>

Is there any way I can avoid writing a stored procedure to accomplish the following. I have a field that contains dimenstions (Length X Width) and I need to parse out each of the dimensions and store them in another table in two separate fields. Size: 8 3/8 x 10 15/16" 8 1/2 x 11" 10 x 1...more >>

Sorry if my idea is bad. I'm working as a consultant for 2 companies. I have a full access to the server (including SQL Server) of the first company. I have no problem with changes in a database (new SP or altered SP, tables, whatever). VB6 executable on each workstation is started by a small ut...more >>

Hi Everyone, I have some question,I need to catch errors from SQL server,when Inserting data into tables,when updating tables.How can I catch different kinds of errors and insert it into error log(table)? Thanks a lot, Marina...more >>

hi I have this table : ID Desc a desc_aa_1 ab desc_aa_25 s desc_aa_3 p desc_aa_2 I need to sort this table by number of Desc column i know just one : number coming after last ( _ ) char. a desc_aa_1 p desc_aa_2 s desc_aa_3 ab desc_aa_25 Than...more >>

Hi all, I have simple question: how I can show values from dataset in toolbox ? I don't want first, last, or sum, I want in one line all values from one dataset column. Thanks. -- Ing. Branislav Gerzo...more >>

Hello! could be in Properties box in table/matrix "do not show 0 values" ? If yes, I don't have to write functions like this : =IIF(cdec(Fields!p_tsp_ef.Value)+cdec(Fields!p_pm10_ef.Value)+cdec(Fields!p_pm25_ef.Value) -cdec(Fields!p_tsp_nf.Value)-cdec(Fields!p_pm10_nf.Value)-cdec(Fields!p_pm...more >>

Hi,I am working with Vb6 (Sp6)-Ado lib. 2.8-and MSsqlServer 2000 . My problem is that my update stored procedure is working fine with the "Query Analyser" but sucks in VB IDE… I use a asynchronous connection to the database. Vb does receive an event (ExecuteComplete) without errors: status=ad...more >>

All- I'm getting a syntax error while trying run the following: Declare @dtAccountDate datetime, @dtContactDate datetime, @dtOpportunityDate datetime Select @dtAccountDate = Getdate() - 5 Select @dtContactDate = Getdate() - 5 Select @dtOpportunityDate = Getdate()- 1 SELECT Max(Recent...more >>

Uncle! Anyone have any pointers for trying to do something like this (w/o DynamicSQL or multiple statements): Declare @Yep int, @SomeField1 varchar(1) Set @Yep = 1 Set @SomeField1 = 'D' Select * From SomeTable st Where st.SomeField1 = @SomeField1 AND st.SomeField2 IS Case Wh...more >>

I have created a view that sums two columns and subtracts the amounts to get a total. SUM(In) - SUM(Out) AS Status The way the data is in the In Out columns will only allow for a 1 or 0 to be returned from the expression. I want to now take that 1 or 0 and have a 1 become In and a 0 beco...more >>

this works (believe it or not) I'm not a vet. SQL programmer so there may be a better way. Here is what it does it looks at a table with two integer values XBH and OBL and it will return the first occurance when (XBH - XBH(previous rec) > 0 and (OBL(previous rec) - OBL <> (XBH - XBH(previou...more >>

have a 3 int varibles: y,m,d how get Date from them? this is the only way? convert(smalldatetime,convert(varchar(4),@y) + '-' + convert(varchar(2),@m) + '-' + convert(varchar(2),@d)) ...more >>

Hello All, I know how to have SQL not use duplicate values, but what if I want one value, in this case "STOCK" to be used over again, just nothin else. How would you do that? TIA Rudy...more >>

How can I capture the updated data in a column and then use that to update column in another table? ...more >>

I tried using sp_help and outputting to a file. This produces a Crystal Reports .rpt file that Visual Studio will not display (invalid TVL record error). I also tried downloading a third party Crystal Viewer, but it can't display the report either - it comes up blank , probably some kind o...more >>

Hi, I have a function defined that takes a comma delimited list that is passed to a sp as a parameter, this then creates a temp table that I can use in my sp that is calling the function: CREATE FUNCTION Split (@List varchar(1000)) RETURNS @Results table (Item varchar(1000)) AS ...more >>

hi all, I have developed a program in VB and created my database in SQL Sever. Now one of the tables in the database is giving me problems, when I try to add a new record in a table it replaces one of the old records. I tried to icrease the size of the database as I thought it was the sourc...more >>

Hello, I'm using Select into statement with identity and collate for order by. My command is : "SELECT Identity(Int,0,1) as RowNum, * INTO tempdb..tmpIx_22 FROM (SELECT * FROM V_DocumentsOutMain Src ) Tbl Where ID = '36' Order by DocTypeID ,DocNum COLLATE HEBREW_BIN" DocTypeID : int Doc...more >>

Hi, Trying to create a sproc that will allow for records to be deleted in one table only if the value doesn't occur in a related table, i.e. if deleting that record doesn't violate referential integrity. If it did violate referential integrity, then I want to be able to set another field, ...more >>

Hi, I am having problems retrieving result the way I need using for xml explicit. I am querying two tables to retrieve the details. Below is the way I need my results: Table 1 having grade and table 2 having grade against a memberid. My schema is such that I need result in the format show...more >>

Is it possible to have a select that produce consecutive days in some interval? E.g. (interval 2003-09-01 - 2003-09-19) 2003-09-01 2003-09-02 2003-09-03 2003-09-04 ..... 2003-09-18 2003-09-19 Thanks Mihai Fantana...more >>

Hi all, I have a working table with a flexible number of columns. I have several views upon this table with joins with other tables. These views use SELECT FlexibleColumnsTable.*, ... FROM FlexibleColumnsTable INNER JOIN ... Sometimes (I am still unable to determine the reason) the results ...more >>