Hello ! I made a function that calculates percentile and returns decimal But one slight problem is that it's not dynamic and I want the function to some how look at every row of employee id and return values for every row of employeeID Thank you TKim select distinct employeeid, dbo.o...more >>

I have posted the same question, but since it did not appear in the thread, I am reposting it. Hello All, Thanks for your previous query feedback, I wanted one more help of such kind SkillValue_ID EmployeeNo SkillType ------------- --------------- ----------- 3 ...more >>

Hello All, Thanks for your previous query feedback, I wanted one more help of such kind SkillValue_ID EmployeeNo SkillType ------------- --------------- ----------- 3 C_0014 3 6 C_0014 1 9 C_0014 2 1 Q...more >>

I am developing an application that stores various data into an SQL table. The problem I have is that all inserts seam to work properly. However, when I rightclick the table in SQL's Enterprise Manager and choose Open Table/Return All Rows, some of records currently inserted do not show. Ho...more >>

Hi, I want to create a procedure that checks whether a server is updated or not. I compare a number on the client with a number on the server - the one with the smallest number is not updated. Here is some code from my procedure: ------------------------------------------------------------ ...more >>

I am trying to write a query that will return the following information sorted by region: Region description Total orders for the period selected Total "ASAP" orders for the region Total "NON-ASAP" orders for the region Orders with quote time <= 50 minutes for the region Orders with quote t...more >>

Hi, Good Day! In my SQL database, I define my ClientCode as varchar. For eg: A12, 3456, 987, B19 I faced problem in sorting those records in order. Wrong sorting: 3456, 987, A12, B19 Correct sorting: 987, 3456, A12, B19 What shd i write to convert only those varchar that are in numbers so ...more >>

Hi all..... what function or special way is there to determine how many numbers i want to show after a decimal point? For instance i want to use this format ###.## i saw ppl writing about a function?! called DECIMAL....but my SQL doesnt recognize that....and format doesnt work too..... all i ...more >>

how do I do that? Convert 2003-07-14 13:54:58.000 TO 07/14/03 1:54PM ...more >>

hi i need to merge two table ... without duplicates, and i need to prefer the rows of thhe first table. If there is the row with the id =1 in the first table i dont add the row of the second table with id 1 Thanks a lot i'm beginner with sql Carlo ...more >>

Did any one know how to capture locks information in Sql Profiler . I have selected in events tab locks and data Column tab I set it to default. And I have created blocking and dead locks in the database. When I saw in profiler I am not getting any usefull info. I am getting just Lock:Rele...more >>

I am trying to group by a range of values. For example. OrdersTable: {ClientID,DayOfWeek,Amount} 1,MONDAY,10 1,FRIDAY,12 1,SATURDAY,18 2,MONDAY,10 QUERY: SELECT SUM(Amount) FROM OrdersTable GROUP BY ClientID,DayOfWeek The problem is, I would like to combine Monday through Friday in the...more >>

I have a view then when I query it like this: select Address.id as AddrId, ClmId FROM dbo.Address INNER JOIN dbo.Entity on Address.id = Entity.Address JOIN dbo.viewClaimsEntity on Entity.id = viewClaimsEntity.EntityId WHERE ClmId in ( 1 ) I get a very prompt result (<1s) But if I do this ...more >>

I am having an interesting problem when adding data to a table. My application gathers all kind of information and then stores the data in a table. No exception is thrown, everythng seams to be working well but when I do Open Table -> Return All Rows, SOMETIMES the added row is not displayed ...more >>

hello, ich have to check in a stored procedure ,what databasefilesof a special name pattern are located in the Data directory (but they are not attached!) i get the results in the Query Analyser like this : Declare @DBDirectory varchar(500) Declare @execute varchar(500) set @execute=' ...more >>

Hi, I want to do the followings, --------------------------------------------- @current_quarter + function(1) @current_quarter + function(2) @current_quarter + function(3) @current_quarter + function(4) let say I have @current_quarter = 3 by adding function(1) will go next 1 quarter, ...more >>

I have SQLServer 2000 SP2 I am getting this error when trying to restore a database. The percent says 100% complete but I get this error. Thank you for any help! Rich Here is the code: Dim oServer As SQLDMO.SQLServer Dim Files() As String Dim FileCount As Long Dim ...more >>

I'm trying to come up with an effective way to manage many image files, and metadata about those images. My photographer clients tend to like to write images directly to a filesystem on a network share, rather than dealing with the kludge of working through a web application. What would be nic...more >>

I need insert formated text (exs. FullName with Bold formated fonts) from one db.tablename.narrative (type text) to another db.tblname.Nerrative(type text). I tried to use only statement like: Insert into TableName ( col1, col2, Narrative,..) values ( 1, 'AAA', FormattedTextFRom Source Table,...more >>

hi All, I was wondering if anyone could point me to some articles or material which details how and when exactly does ado recordsets make roundtrips to server to fetch records, also one of the discussions we had was that whether in all conditions ado recordcount property will fetch the corr...more >>

Hey, thanks to Dan and Falik so far. I noticed after doing ALTER TABLE Products ADD CONSTRAINT DF_Products_Price DEFAULT (0) FOR Price that the price column was no longer required. I think NOT NULL should be in there somewhere but :) as a newbie I'm not sure what the t-sql should lo...more >>

Hi All - I have a user that does heavy data entry in some large fields in our Database. Is there any way to integrate a spell check utility into SQL Server? Any spellcheck at any level. Thanks in Advance, Kameron...more >>

Hi Friends, I am looping the below query 100,000 times because I am unable to write a 'SET BASED' query. Can any one help me out to write simple set based query. Thank you in advance. ----- WHILE (@Event_ID < 100000) BEGIN SET @execSQL = ' IF EXISTS ( SELECT * FROM GFDM.Event WHERE Ev...more >>

Hi, I have a table with 2 fields (firstname lastname and companyname), but no key. I am using SQL Server 7. If I insert 2 records one by one from an ASP script. I inserted "John", "Mortem" and "AJ company" firstly and then "Lisa", "Wang" and "AJ company". And then I try to retrieve the tw...more >>

I need convert 0 to 'No',1 to 'Yes' . Is there anyway except function 'Case'. In my view design if i use 'Case', the diagram cannot show up. ...more >>

Hi, I've got a stored procedure that accepts one input parameter and returns a resultset. How can I view the resultset in Query Analyser? Thanks, Kevin. ...more >>

I have several stored procedures inside of a database. Is there any way to search them and find which procedures contain a particular string/value. I have to change a hard coded value inside several procedures and I need a quick way to find which procedures contain the specific value. ...more >>

Is it possible to assign the result set of a Select statement into a variable, eg. if my result set is abc def ghi can i store this one column into a variable of type varchar (8000) ?? OR is there any other way to do this, the reason is I have a comment record in a table and I want to...more >>

hi verifing whether a table with same name exist or not before creating new table using asp i hope to get asw soon akash...more >>

My @subject doesn't show up as the subject of the email. Is there a trick to something so simple? EXEC xp_sendmail @recipients = 'robertk;laurac', @message = 'The master database is full.', @copy_recipients = 'anned;michaels', @subject = 'Master Database Status' Thanks ...more >>

I have 2 tables, user and country AND I would like to SELECT all users from the country of current user (up_id=1): userTable ------------ up_ID up_country_ID up_name up_address ........ CountryTable: ----------------- country_ID country_Name select up_name from userTable WHERE u...more >>

I don't know if this is a SQL Server issue or ADO.NET, but here goes- I have a sql server table with a Price column. The prices are in the format "1.23", but (this uses an ADO.NET datareader) it's being read as "1,23" which causes a problem.Is this a setting in SQL Server? Tks Justin Dutoit ...more >>

Hi, I'm sending an email with xp_sendmail and trying to use this concatenate a variable to text, e.g.... "@message='Hello, please repond to '++ @Rep." @Rep is my variable (like you didn't know that), and it should read "Hello, please resond to Microsoft". But I get this error "Incorrec...more >>

Hello, I am attmepting to use VB Script to send email from a job step. Does anyone have a VB Script that sends out email from a job? I do not want to use xp_sendmail... I want to use VB Script. I am running Windows 2000 Server and SQL Server 2000. Please advise... Thank you, B...more >>

Hi, can someone tell me how to drop a table using the information_schema.tables view. Thanks Chuck ...more >>

All, First, thanks for all help that you have provided me for all my postings. Which class would be helpful to take if I wanted to get good at SQL Programming, espcially Triggers and Stored Procedures? I took Programming a SQL Server 2000 Database but my instructor didn't really go i...more >>

Hello: I need help on writing a query that will compare data between two tables (that have the same columns & data types). We just need to compare the data in each column between those two tables. There are about 50 columns. I am thinking about doing "Where" clause to compare each column but ...more >>

How do I use a function in query analyzer in a 'where' string clause? Is there an escape character? EG: If I wanted to do something like...WHERE FieldName LIKE '% Left(SomeField, 1) %' Thanks, Chris ...more >>

All, I have a DTS that periodically checks for new Zip files in a particular directory. If they are present it extracts them. However, I now want to move the Zip's after they are extracted. I use an Activex task to do the looping and extracting and I originally though that I could just a...more >>

Hi ! Is there any idea how to test SQL Server 2000 on Performance with SP3 instaled and without it ? Is there any tools which do this ? Thx ...more >>

Hi, i want to update my Customer table in my SQLServer database. I want to check whether each field in the table has a value assigned to it - if it does, i want to check whether the new value (the one that the user wants to put in the field) is null/0 or not. If it is null/0, i want to keep ...more >>

Hello, Someone knows the way to detect in SQL Server 7 when it is overload, this is, how many process existing running, and so on. I have this problem, from my asp page I run a stored procedure, but sometimes my server SQL7 is overload or have many process running there for instance job...more >>

I'm using VB.Net programming and I have a Function built that takes the name of a stored procedure and two variables. These are sent to SQL 7.0 in order to delete, update or insert records in my tables. I am using this same process on two other tables and it works very well but for some r...more >>

Hello, I would like a table column to default to the fiscal year based on current date. If datepart(month,getdate()) > 6 then FiscalYear = datepart (year,getdate()) else FiscalYear = datepart(year,getdate ()) + 1. In other words, fiscal year 2004 begins July 1, 2003. What is the ...more >>

My understanding was that if the isolation level on a connection was READ COMMITED that a select statement would request locks with mode shared. But when I run a simple select * from the query analyzer, the locks generated are intent-share and intent-exclusive. These intent-exclusive locks seem t...more >>

Hi All, I have a DTS package that takes a considerable length of time to run. I've managed to get the time down to around three hours by adding a number of indexes to the various tables. This is fine. However, the tables are re-created each week, without indexes. I have no control over t...more >>

How can I successfully pass a date parameter to an exec procedure? I have the following code. declare @tablename nvarchar(30) declare @d_startDateTime datetime declare @d_endDateTime datetime set @d_endDateTime = dbo.f_GetDateTime(getdate (),'143059') --cutoff date and time set @d_startDa...more >>

Hi, I need to create a trigger on a table to run another process that will take the new row and process/filter it, and then transport it to various other destinations (using ftp, etc). Writing the trigger is pretty easy, but I'm wondering, what is now considered to be the "best" way to interfa...more >>

I have a table with roughly the following values: (each restarant will have such a table) MenuItem Vegetarian ======== ========== Fish & Chips NO Beans YES Cake YES Beef NO Corn YES Steak ...more >>

Please can any one help? I have two tables a Header and a Detail Table. The header table has a PK called Forecast_No This has a foreign key relationship with a concantonated primary key in the Detail table consisting of the Forecast_No and a Forecast_Line_No. For instane Header.Fore...more >>

It is known to all of us that there isn't any one-step paging SQL statement in SQL Server. On the net, I found several solutions for resultset paging with TSQL. 1. Select ordered records into a temp table which has an indentity column (id), and then Select between id>a and id<...more >>

Hello All, Is there a way to run sql select statements with column numbers in place of column names in SQLServer. Current SQL ==> select AddressId,Name,City from Address Is this possible ==> select 1,2,5 from Address Thanks in Advance, -Sandeep...more >>

I've got the following update: update a set extractortypeid = b.extractortypeid , co2valveid = b.co2valveid , bottomsealtypeid = b.bottomsealtypeid, containerno = b.containerno , necktypeid = b.necktypeid from dbo.CONTAINER a...more >>

what is the correct way to call a procedure specifying output parameters?? like this: EXEC sp_CreateGLDetail @err_code = @err_code OUTPUT, @err_desc = @err_desc OUTPUT, @err_sql = @err_sql OUTPUT or: EXEC sp_CreateGLDetail @err_code OUTPUT, @err_desc OUTPUT, @err_sql OUTPUT or do th...more >>

Dear All, I am in a development environment with 8 developers all of which have rights to make structure changes. As they can be an undisplined lot (we have tight deadlines) sometimes they make structure changes to the db without telling me. Is there a way then other than denying them...more >>

hie, i am transfering some queries from access to sql server views. in access, there is one line of coding that i am not sure how to transfer it into sql server. The microsoft access coding goes like this: Trim([IFSAPP_MMR].WO_NO]) how can achieve the same effect in sql server? thanx! ...more >>

Sql Server 2000 Update Trigger Question Is there any way to dynamically set the column name when using the [ { IF UPDATE ( column ) ] trigger conditional statement. I have tried creating the statement as a string and executing it and that did not work. example: {set @sql = 'If upda...more >>

I am trying to execute a stored procedure from an asp page that executes a dts package and the results from the error object reads: "xpsql.cpp: Error 997 from GetProxyAccount on line 472" Is there a better way to execute the dts package from the asp page? thanks in advance....more >>

Can someone please explain me the error that I get when i run the DDl in the query analyzer. I Created the db schema using Visio Enterprise architect. HServer: Msg 1776, Level 16, State 1, Line 3 There are no primary or candidate keys in the referenced table 'MerchandiseOrders' that match the...more >>

Any assistance w/xp_cmdShell would be appreciated. I'm reluctant to start changing any logins before I have a better understanding of what login/element to change. I'm getting Access Denied when I'm trying test running a simple DIR command. S:\ is a mapped drive on another MSSQL Server. I'...more >>

Hello, I am going to create new tables for a database. Wanted to know whether creation of IDENTITY Column and setting it as a primary key is good OR Creation of column with Datatype UniqueID and setting a default with NEWID() and then setting the column as Primary key is good. Wh...more >>

I have many table with more than 250 columns. I have to dynamically generate script to build the column names in the select statement. Due to the varchar(8000) limit half of the column names are getting truncated. What is the best of doing it. My sample script: Declare @tableNames table (id...more >>

Hi there. Does anyone know how I can retrieve the names of the fields in a table with only the table name? I'm looking for a recordset of field names returned from something like "SELECT fieldnames FROM table" Any help would be appreciated, Len....more >>

HI, I'm currently working on a view to display production information, the view is broken down over a 24 hour period, displaying each production line, what it manufactured etc. The view is over a table which contains data on every item produced and is grouped by hour. This works fine provid...more >>

Hi, i just set up a Linked Server in Enterprise Manager, and wonder how can I select data from a table in the database? The linked server I named MES, the database is LV2 and the table is "MyTable". How do I select all records from MyTable ? Regards, Bent *** Sent via Dev...more >>