Hi I am trying to retrive a field from the database , say the quer returns me two rows with value 20.572 and 10.3 but when i get the value in the code is get it as 20.572 and 10.372. If I change the order of these rows and make it as 10.3 and 20.572 then i get the correct value 10.3 and 20.572...more >>

Hai, can anyone out there help me in this following query. i have two tables as shown Table : A KN PN Area ---- ------ ------ 100 200 1.0 100 201 2.0 Table : B KN OwnerName ---- -------------- 100 Owner1 100 owner2 100 owner3 100 owner4 100 owner5 100 owner6 What i...more >>

Will there be any difference in performance if I use a NUMERIC (with precision =0) datatype as Primary Key in place of an INT datatype . I have tested this scenario with ample data but couldnt see any difference in Performance. Though it is recommended that REAL or FLOAT should not be used as...more >>

Lawd I can't stop laughing! I challenge anyone to describe what's wrong with this SQL using *50 words or less*! The description: http://thedailywtf.com/ShowPost.aspx?PostID=27097 The actual SQL: http://www.thedailywtf.com/images/view-to-rule-them-all.txt ...more >>

Total newbie at this....so sorry if question has been answered before. I need to write a select statement that will include details that I will dictate but fields from tables within database. eg application needs to print out letter automatically when a certain condition is met. Using Rep...more >>

I have a stored procedure that our organization has been using to display a report that has been working fine for over a year now. Within the last week this particular stored procedure has slowed to several minutes or more depending on the date range selected as parameters. The strange part is...more >>

Anyone care to attack or defend the following query syntax: select <column list> from <table list> where colA = isnull(@varA,colA) ...more >>

Hi, TABLEA ------- CustomerID -unique identifier Name - varchar DateRegistered - datetime #ofDaysOff - int #FreeDaysOff - int My problem is that I need to get to get the employeeid of most senior employee with a criteria of 2 days off. I have tried this query but I dont get the corr...more >>

Hi All, I have a table with the following format: id, char(2) issue_date, datetime return_date, datetime and the data generally looks like this A1, 1/1/2002, 2/1/2002 A1, 2/2/2002, 3/1/2003 A1, 3/2/2003, 4/1/2004 A2, null, null A3, 2/1/2001, null A4, 2/2/2002, 3/1/2003 A4, 3/2/2003, ...more >>

I'm importing a batch file using DTS although I could use other means. The data is unstructured, there is one column in the table that corresponds to a single line in the batch file. I'm trying to select a record in the table like: SELECT Col001 FROM dbo.batch WHERE (Col001...more >>

Have query that returns the First, Middle and Last name of names and places a blank in the place of a records that has a NULL value for Middle. How can I have it place a blank space for a null without adding a character place. Instead of the restult being John Doe it is John Doe (additional s...more >>

I'm running SQL Server 2000 on a Windows Server 2003 machine. We have sp3 on SQL. I am getting an error when I query against my views. The views use a linked server that grabs data from FoxPro dbf files located on the same machine. In resolving a memory issue, we disabled the 'Allow In...more >>

How to write T-SQL to 1. remove a database from participation in replication 2. kill any processes attached to a database (SEM > Management > Current Activity > Process Info > {process name} (right-click) > Kill 3. delete the database (The basic objective is to delete the database.) And...more >>

I am adding an int field to a table that will be used to link it to another table when it has a number in it. I plan to index it for faster access but wondered if my default for that table should be NULL or zero or if it makes a difference. Thanks. David ...more >>

Hi I am creating a automated script using BOP to output formatted output files bcp "tmp_sales" out sales.rpt -S DATABASE -U user -P pswd -f sales.fmt my format file is (sales.fmt) 6.0 10 1 SQLCHAR 0 6 " " 1 c_code 2 SQLCHAR 0 6 " " 2 c_type 3 SQLCHAR 0 3 " " 3 c_period 4 SQLCHAR 0 4...more >>

I have two tables Table1 id, barcode, machineid Table2 id, currentitem, machineid Both tables have mutiple rows per machineid. I want to get one row that will have barcode, lastitem from last rows from both tables. Can someone help, please. ...more >>

How do I enter <NULL> into table column via EM? I thought it was Shift + Enter or CTRL+Shift + Enter - but no dice. Thanks. ...more >>

Hi, I'm new to SQL. Recently I need to setup a schedule job to export data into csv file every month. I know I can do the schedule part, but I need help with writing exporting data script. select * from tb1 to file info.csv Thanks in advance, Sarah ...more >>

Hi all, Is there any way to evaluate a string, say in a stored procedure, as though it's a command to be exceuted? I'm curious in general, but my specific issue is that I'd like to have a table defining various temporary table layouts and on the fly be able to read from it and build a temp...more >>

Hi! Here is what I need to do: I have 2 tables; the first called "IDs" and the second called "Test". This 'Test' doesn't have an identity field, and I need to have one to do some filtering, but for burocracy I can't simply add another column to the table. So, as sad as it is, I had to create a...more >>

I need to send a daily email reminder to persons who have an appointment with us. The daily email reminder needs to be sent the day before the appointment to all persons who have an appointment on that day. What is the best way to do this in SQL Server 2000 on a Windows 2003 Server? tbl...more >>

Is there a way to have a column in a query result that is an "autonumber"? Say I have 10 records returned, I want them to be numbered 1 - 10. I'm sure there's a way using a stored procedure or something, I just can't think of a way. Thanks in advance. -- Chuck Foster Programmer Analyst ...more >>

I have a table with two columns item_code and Alternate_reference. During a select I would like to concatenate Alternate_reference if there are more than one for an Item_code. Is this possible and if so how do you accomplish this. Here is and sample of the data Item Code Alternate_r...more >>

Hi guys, I have a dilemma, I used the following query to find the Q-by-q median. It uses a temporary table. I think it's because it has no index and foreign key, it takes over 5 minutes to run. When we look at subquestions, there are over 10,000 records and cross join takes forever. I don't kn...more >>

Hi, How do I join 2 tables with null value return? Table1 Month, Year, Hours Table2 Month, Year, Accounting_hrs Here is my current statement. Select Table1.month as MONTH, Table1.year as YEAR, Table1.sumhrs/Table2.accounting_hrs AS AVERAGE from Table2, ( select month, year, sum...more >>

Hi All, I'm trying to figure out and learn how to do the following: /*I have the following query which works:*/ SELECT DataSource AS [Service Line], NamePrep AS [PO Created By], COUNT(PoNumber) AS [Total Of PONumber] FROM OPW WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '0...more >>

Hi all, Something strange is going on: When I do a select * from table some of the data appears to be in the wrong field. However, when I look at the table contents in the EM, they are all correct. When I do select statement in the QA based on criteria, the correct rows are not returned. A...more >>

I have written a stored procedure, based on the sample code to send email. Only problem is that the message body is truncated to 255 characters. Any ideas? Thanks! The procedure is listed below. [It has an extra sp_OAGetProperty statement to retrieve and print the message body after it ...more >>

I copy several backup files daily to an E:\ drive on a server. I would like to use the xp_cmdshell to find all of the files that have the BAK extension on the E:\ drive and save them to a variable. Then test each variable with the following command: restore filelistonly from disk = 'E:\...more >>

I am trying to put today's date into a DateTime field in a table. Is there something I can put in the Default Value property of that field to do this? I used to do this in Access, but can't figure it out in SQL Server. Thanks in advance, -- Chuck Foster Programmer Analyst Eclipsys Corpo...more >>

I am trying to use sp_help_targetserver to retrieve the time_zone_adjustment from msdb on the local server. "EXEC sp_help_targetserver" returns the fields with no data. "EXEC sp_help_targetserver 'servername'" returns an error "The specified @server_name ('servername') does not exist.". I tri...more >>

I have three tables (SHADES, CHANGES, AND SHADES_SUC) Shades primary structure: Shade_ID Changes primary structure: Old_Shade_ID Shades_SUC primary structure Shade_ID A record is written to Changes when a change is made to the Shades table and some of these changes will cause a scenario w...more >>

I have a series of stored procedures that are called from an outside application. This application supports multi-threading, so I can have 10 instances of these stored procedures running at once. This is causing a great deal of locking. As I am working through the code, the biggest offenders a...more >>

Hi All, I want to pass a string array to a stored procedure. Can anybody suggest a way to access the elements of the array in the stored procedure? Thanks kd...more >>

I'm totally flabbergasted, In Enterprise Manager, the column will fill with the Default Value when no data is entered as it should. However, in my Stored Procedure, it won't. Table: Col1 uniqueidentifier pkey not null default newid() Col2 tinyint not null default 20 Procedure: @Col1 UNIQU...more >>

Hi As far as I know databases have a few models, relational, network , sequential etc. With SQL2005 using the .Net framework more and more, will we be looking at the future of DB's changing into full OO ? Any comments will be appreciated. ...more >>

How would I go about ordering results using a specified order. Say I have non-unique ids, (23, 12, 98, 3) and I need to return rows that contain these ids in the order I specified above. How could I do this without using any extra queries or sub-queries or insert/retrieve from temp tables? ...more >>

I have two tables. The first table contains data that are leaves in an hierarchy. The second table contains the parent-child structure of the hierarchy. I use table1 (leaves) as a foreign key to make sure that data in other tables can only be added at the "lowest" level. I have an insert tr...more >>

Hi all gurus for query select object_name(id),name,* from sysindexes where name like '_WA%' It gives me result with indexes in the database. I want to know what are this indexes generated by sql server and how can I use them for my performance. Thanks Amish ...more >>

Hi NG Im trying to make a trigger, that runs a SP. Running the sp is quite easy, but the problem is that it is enomously complicated, and sometimes it fails. Ok, I should modify the danm sp to make sure it never fails, but chance is like .1% and fixing the problem will simply take up too mu...more >>

Hi, My table has the following structure create table CompanyRights ( CompanyID int, RightID int ) Insert into CompanyRights select 1,1 Insert into CompanyRights select 1,2 Insert into CompanyRights select 1,3 Insert into CompanyRights select 2,1 Insert into CompanyRights select 2,1 ...more >>

i need to return the number of rows from a query as something eg select ID,column from table where column = something then return ID,column,expr1 (which is the total of records returned) is this possible ? my query looks like CREATE PROCEDURE CSR_Performance @csr varchar(75), @Fro...more >>

Hi, im trying to create a stored procedure that runs a few different querys on different tables and returns me the row counts for each query as a seperate column in a table is what im tryign to do at all possible? and if it is how do i get the rowcounts of each query into a seperate column ...more >>

Hi All, Is it possible to use Case statement inside Joins? Say, Select .......... from TableA A inner join TableB B on Case When @filter <> '' Then B.ID = A.ID and B.ID in (Select id from temptable where Name = @filter) Else B.ID...more >>

Hi all, I have a database that the transaction log has been set to have minimum log file size to 500M on production environment. Now, i backup the db and restore it to testing environment. Since the testing environment does not have enough space, so i shrink the db. However, due to the 500M ...more >>

I have a website that collects data from users. I want to provide a search facility where users can search the database for combinations of key words. Can somone point me in the right direction as to the best approach? Thanks. Jay ...more >>

I just moved an ASP.NET application to a new web server at a different location of ours. The SQL Server machine was moved to that new location at the same time as the web site DNS was pointed to the new server. Suddenly I'm getting lots of intermittent errors of this type. The switch/hub/re...more >>

I know that ADO with SQL Server has connection pooling feature. Considering this, I cannot understand why the problem described below occurs. Please reply me any solution. Thanks in advance. Regards, Hyun-jik Bae ----------------------------------- Environment: SQL Server 2000 ADO 2....more >>

Hi everyone, I've 2 windows 2000 server running each own instance of SQL2000. I've = setup both linked servers @ both end. At server A, it'll call a sp in server B, whereby this sp will update = server B tables based on server A's data. And the server A table A will = trigger back to server B...more >>

I know that ADO with SQL Server has connection pooling feature. Considering this, I cannot understand why the problem described below occurs. Please reply me any solution. Thanks in advance. Regards, Hyun-jik Bae ----------------------------------- Environment: SQL Server 2000 ADO 2...more >>

Hi, After I copied some views from other database, I lost all records in five tables. Unfortunely, I didn't have the backup file. I tried to use Lumigent to restore from the Transaction log, and three of them have been recovered successfully. But still have two tables cannot restore. A...more >>

Hi, I've just detached using QA and sp_detach_db a database that I have been working on for a client and taken it to the clients site and using QA and sp_attach_db attached it to their SQL 2000 Server. The problem is that a) there are none of my stored procedures for that db on their server, ...more >>

Hi. One of teh major problems we are facing is happening when we change the data base structure for new releases and we want to dispatch this to existing clients. 1.Is there a utility that allow us to apply the new schema on an exiting data base. 2. Is there also a utility that will lis...more >>

testing new news server apologies Mike ...more >>