Hi, I need a query which returns all rows from one table and rows from another table if they are present. I also need to restrict the right hand table to certain rows. When I do this on;y the matching ros from RHS show. How d I get all rows from LHS and only atching rows in RHS? Man...more >>

Hi There, Is there any good free tool available for formatting sql Thanks ...more >>

Hi, Please check two statements below, one is Update, and another is Select statement based on that Update. Why they have different execution plan? Problem is that Update statement first joins two titles tables instead to first join titles and sales as Select does. It makes me problem on larg...more >>

Hello Gang .. I have a dynamic stored procedure which needs to be able to process as part of it's search a form field which may contain several words seperated by a space. ie: earth diamonds brazil ocean My dynamic stored procedure works great, right now the parameter containing the form in...more >>

Hello All! I have two DDL, one for Color, red,blue, green the other for size, small, med, large. So basicly when the user picks red and small, I will get this. I know about putting the string for the ddl and stuff, just keeping it simple. SELECT Pipes FROM Products WHERE (C...more >>

hello to all how can i see source of prceure after i pass its input parameter thx ...more >>

Hi, We have 8 sites running each one a SQL Server. Clients can connect to any of these sites to get information. Now we want to have an 'extra site'. Clients that connect to this site will be able to get information from the previous 8 sites. I mean, this extra site, will contain all the da...more >>

hello to all how can i see source of prceure after i pass its input parameter thx ...more >>

i have a datetime field that i'm just storing the date like 5/17/2005. i need to add the time like 10:30 PM to it. Should I store the time in another field and concatenate? Right now, i just have a calendar date picker on user form and will add time drop down fields to handle time. any sug...more >>

Greetings, Very simple question, how do i assign a column same name as its value? declare @seas varchar(5) set @seas = 'Donie' SELECT @seas as (??????) NOT static please, dynamic. So, SELECT @seas as 'Donie' won't do me any good. Much obliged, Don *** Sent via Developers...more >>

I understand that to create a temp table in tempdb using a user-defined type, the udt must be defined in Model. However, this doesn't work in my situation, due to security limitations being placed on the Model database. (I am using SQL Server 2000). I'm wondering if there is any viable workar...more >>

Given a table Positions: CREATE TABLE [dbo].[Positions] ( [AccountID] [int] NOT NULL , [SecurityID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Quantity] [int] NULL ) ON [PRIMARY] GO INSERT INTO Positions (AccountID,SecurityID,Quantity) VALUES (1,'A',10) INSERT INTO Po...more >>

A quick question... admittedly based in ingnoranceWhat is the significance of having to prefix the function system function fn_listextendedproperty, or other system functions forthat matter, with the two colons?SELECT * FROM ::fn_listextendedproperty(default, default, default, default, defau...more >>

I know this has to be possible with out using a cursor to loop through this.. say I have tables like this... Table A ========== ItemID INT Item TEXT Table B ========== PersonID int ItemID int (from table A) Table C ============ PersonID Item ItemID Descripti...more >>

I want to assign a datatype to a column that keeps track of the status of a certain process. The column will be named Status with a single digit flag. Something like Status = 1, Status = 2, etc. I'm looking for an appropriate datatype for this column. Which of varchar(1) and int would ...more >>

What is the syntax for changing a table name? Can not find it in my reference books. Thanks Laura K ...more >>

Hi, I am getting a "syntax error near view" when trying to run this piece. Strange thing is that everything runs fine in the query analyzer but not in this procedure. Thanks! --- CREATE procedure TempCumulative @ZID varchar(20), @FromDate varchar(10), @ToDate varchar(10) as begin ...more >>

Hi All: In my J2EE application using SQL Server 2000,I am using the setFetchSize() of PreparedStatement to overcome out of memory error but using setFetchSize() has been a downside .When the resultset is null ,the program hangs at -- rs.next() for more than 30 min . Pl guide me in solvin...more >>

Hi, Please could someone provide me with a best example of how to query records from different tables in different databases, where the databases are located on different servers on the same network. Your assistance is much appreciated. ...more >>

If I have a table with an image column, how can I add an image file to the image field? Using ADO.Net I can use the command object to add text data like this: cmd.CommandText = "Insert Into tlb1(fld1) Values('test1')" cmd.ExecuteNonQuery() But what if I want to add the file at C:\dir1\pi...more >>

I added a field to a table the other day, and ever since then views that use this table are exhibiting bizarre behavior. Consider the following table: Field A Field B A1 B1 A2 B2 A3 B3 I then alter the table, and add the following: Field C C1 C2 C3 After adding the field, a vi...more >>

Hi, I have the foll table sid prod qty abc 9455 12 abc 123 10 dfg 123 10 How can I display the data as sid prod9455 prod123 abc 12 10 dfg 0 10 Thanks ...more >>

I've upsized my Access DB to SQL Server. We have a wesbite (ASP) that hits the database with different queries. Everyday I seem to find something in our code that only works in Access. Today it is our "DELETE" queries. Example: DELETE * FROM ADMIN; Does anyone know a way around this, or...more >>

Hi I drop table employee go create table employee ( fname char(20), lname char(36), dept char(6), in_dt char(6) ) insert into employee values ('Joe','Doe','legal','980622') insert into employee values ('Joe','Doe','legal','990313') insert into emp...more >>

Hi All, I have problem with log files on my databases, they grow so much that I have errors when I backup and restore the data. The best way that I found to fix this is to detach the db, delete the log file and attach the data again. How can I do this programmatically? Tks in advance Johnny...more >>

I have a DTS that pulls data from an Informix database, scheduled in a job to run every 10 minutes. The DTS takes less than 30 seconds noramally. Occasionally, the JOB will execute endlessly (hours/days). There does not appear to be a way to tell a Job to terminate if a certain amount of tim...more >>

Hi, Is there anyway to find out the last altered date of a specific stored procedure. I tried Information_Schema.Routines but the value in that column doesn't change after I modify the stored procedure. When I look at BOL, it said "The last time the function was modified" How to find o...more >>

I'm pretty new to TSQL. I'm trying to write a generic proc that returns either ALL records from a table or (if a parameter is set to 1) or only a sub-set of that table (if parameter is set to 0). Here's an example of what I'm aiming for: Create table contact ( contactID int, FullName var...more >>

The company just got a new SQL Server. *finally*, we now have a dev server and a production server. But apparently, this huge multinational corporation has no one that knows how to set one up. We're trying to set each of them up with a link to the other via "sp_AddLinkedServer" Running...more >>

Ok all gurus, My boss just submitted a request to me and I am not sure how to fulfill this request. The situation he wants to have me handle is when and how to defrag both tables and indexes. I have got the defrag part down for both instances one is to use dbccindexdefrag and the other would ...more >>

Good Morning. I have 4 tables (each approx. 80MB) that I am consolidating into two new tables through vb6 (because there is a lot of analysis as to which of the two tables to store in.) I tried to pull one table into a recordset to process it and it bombs. Is there a slick way to pull por...more >>

I need to have a date that is exporting as this 2005-05-18 to export as 5/18/2005. Is there anyway to write this into the query?...more >>

Alter table to BIGINT. Some size table increase 2x, 3x or 4x. What are the influences when I change some columns from INT to BIGINT ? thanks, Shima...more >>

I am using sp_spaceused stored procedure to get the database_size result. The result comes back as a string such as "512 KB" or "100 MB". I really need a number value representation of the size. I though about parsing the string, but I was not sure if there were any other results of the string ...more >>

I am query analyzer sigen into the wood server and the rwood database. I am trying to run the following in the same session select * from orl-etile.my_db.dbo.periods where the server name is orl-etile the database is my_db the owner that created the table (I checked the properties of the ...more >>

Hi all I looking the way to list the all the databases in a server and get other properites such the size of each one. The list of the databases of are in the master databaes, select name from sysdatabases, but the other properties are inside on each databes (size, last backup...) How i ...more >>

I have a view with the following FROM clause. I want to be able to get ALL matching records from the ProfitCenterCodeSubs table, even if there are none in the RepairOrderTasks table. Thanks. FROM dbo.RepairOrderTasks INNER JOIN dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode = dbo.Em...more >>

I have been using real as a data type, but want to change that to decimal. I am not sure what scale and precision to use, so I am nervous about changing all the tables and stored procedures to decimal in case I have to adjust the scale and precision later. So, I think I will use a user defined...more >>

On our OLTP 24/6 system we have a table with over 40,000,000 rows in it, the need is to remove half of these rows. There are 7 indexes on the table (one clustered, six non-clustered). Since the IT director won't let me move it to a faster server, I have to delete the records month by month. Th...more >>

Hi I am trying to write a query which takes a startdate and a finishdate and merges them into one text string suitable for output in a directory. I'm in the UK, so my dates are usually displayed as dd/mm/yyyy. My desired output format is something like 8-12/5/05 if the original dates are ...more >>

Is there a flag or setting i can add to the beginning of a sql statement to temporarily allow the insertion of a value into an identity column? I'm mainly trying to manually move data from one table to another and make it identical. I'm doing it dynamically through c# though, and i don't want/...more >>

I am currently using DTS to run a query that spits out a txt file of the results - works great, BUT... this code will now need to be run on a fairly regular basis (possibly each Fri or Saturday). Although I am familiar with coding, I am not familiar with having users interface with this code....more >>

i have a inherited adatabase application that creates a table everyday. because a table is created evryday the database is becoming huge. after much deliberation i have decided that i only need to keep 30 days of tables. how can i delete all tables older than 30 days inside the database? i...more >>

I have a SQLServer query that uses a sum function- sums two decimal fields. The returned value is automatically formatted to a length of (38,2). When this table/view is linked into MS ACCESS for reporting it is viewed as a text field due to its length. Is there a way to format the length o...more >>

I have created a recursive function count the number of commas in a string, but it doesn't work and I have no idea why. Code is below. Any help appreciated CREATE FUNCTION [dbo].[reps] (@string varchar) RETURNS int AS BEGIN declare @output int if len(@string) > 1 begin set @o...more >>

Hi, I have the following script to create 3 tables with data. CREATE TABLE [tblPerson] ( [DomainName] [varchar] (30) NOT NULL , [FirstName] [varchar] (30) NULL , [LastName] [varchar] (30) NULL , PRIMARY KEY CLUSTERED ( [DomainName] ) ON [PRIMARY] ) ON [PRIMARY] INSERT INTO ...more >>

Hi, when running the following statement in Query Analyzer to substitute 'dad' by 'mum' in the long-text column childhood of table parent_details set xact_abort on begin tran declare @old_text varchar(8000) set @old_text = 'dad' declare @new_text varchar(8000) set @new_text = 'mum' ...more >>

Dear all, In the SQL Analyzer, when I write a SQL to query a table in a linked server with "Begin Trans", it throws "connection timeout". But, it is ok to run the same SQL without "Begin Trans". Do anyone know how to solve the problem? Thank you K...more >>

Hi ! I'm looking for a tool that can gather "statistics" of T-SQL code (SP, triggers, UDF, Views) By the word "statistics" I mean total number of code lines, number of comment lines, number of constatant expression assignments (e.g. SET @A='Monday' or SET @b=25) Does anybody know such a too...more >>

Hi - I'm writing a recursive function to build a folder list (parent/child relationship) within a listbox in my .net app. This means that at any given time, there could be several concurrent connections open to the database. If someone is working with MSDE, will this just invoke the workload...more >>

Hi all, I have a simple query that retrieves the number of articles that an order has: SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @MyOrder Both ArticleId and ArticleCount are integers. A sample set result woud be like this one: ArticleId ArticleCount 1 3 2...more >>

I've just moved a system that has been running fine on SQL Server 7 over to a new machine running SQL Server 2000, and while nearly everything works as before there have been a few errors popping up that highlight programming flaws (basically, another of the IT staff being lazy in his data typ...more >>

Hi there. Is there a way to debug a trigger? I use a VB6 application running over a SQL Server 2000 database, that has several tables with triggers. Any time I want to create a new trigger, I have to test it thousand times, in order to make it fail-tolerant. I know it's possible to debb...more >>