Hi, Is there any way to delete specific records from a table with out generating the transaction logs. -- Regards, Yash ...more >>

Hi, I use nested loop with inner joins to retrieve organization's chart from EmployeeID-ReportsTo columns. Our client uses XML and I must send the chart in XML format. How can I perform this? Any help would be greatly appreciated. Leila ...more >>

Hi All, We have a huge, complex, poorly designed database in Dev. and I need to find if there are non-referenced columns, which are defined on multiple tables, in this database. I can get column info from INFORMATION_SCHEMA.COLUMN view and FK info from REFERENTIAL_CONSTRAINTS view, but...more >>

I getting an error: "Server: Msg 195, Level 15, State 10, Line 14 'Count' is not a recognized function name." How else can I correctly use the COUNT aggregate around this CASE statement: SELECT distinct clnGUID = '', Community = '', Impact = '', Round(Avg(PopulationKeyInfo),1) as Popul...more >>

I am unable to get what I want out of this query. I am looking for. It seems that I'm getting aggregates (average) from the whole view and not the average of the queried data that is a result after the where clause is applied. What am I doing wrong? SELECT distinct clnGUID = '', Community...more >>

Hi, I would like to create a relation based on a table name. I have a table schema like this Table A ----------- Id Desc Table B --------- Id Desc Documents -------------- TableName Id Records in Documents --------------------------- TableName Id ----------- ------...more >>

I'm pretty new to SQL Server. I know there are a lot of SQL2000 (and 7 and even 6.5) installations out there; OTOH, I like being up to date, especially not learning stuff that will be changed in the new version. Should I learn on SQL2000, or download the beta of SQL2005 and learn on that o...more >>

CREATE TABLE [dbo].[_z] ( [ID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [YEAR] [int] NOT NULL , [COUNT] [int] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[_z] ADD CONSTRAINT [PK__z] PRIMARY KEY CLUSTERED ( [ID], [YEAR] ) ON [PRIMARY] GO INSERT _z VALUE...more >>

Hi, How can i select item and price from the last date date item price 12/11/02 11150PBCAD $0.00 Y 9/4/03 11150PBCAD $60.00 Y 8/11/04 11150PBCAD $60.00 Y 10/7/03 11150PBCAD $60.00 Y 5/5/04 11150PBCAD $60.00 Y 3/24/04 11150...more >>

Hello, I am trying to replicate some data to a disparate location from our current SQL server. Both locations are accessable in Enterprise Manager, but exist on separate SQL servers. I have a "master" database that is the record of source for vendors and customers, and when new vendors ...more >>

Currentnly I have one trigger on a table in Database A that populates a table in Database B. The table in Database B also has a trigger that does a lookup from a table on Database C that is located on a different server. The servers are connected using the sp_addLinkedServers. The problem I a...more >>

Hello everybody, I have a field called "name". Data in this field looks like this: john Doe jane harriet Doe mark h brown I am moving this "name" field data to another table. The new table has fields for first name, middle initials and last name. How can I separate and export/import...more >>

I have the ProcessDate field in the table. When this field is updated I want to set a trigger, which will update Process1Date field on the same record with ProcessDate + 30 days and Process2Date field with ProcessDate + 60 days. Thanks, Michael ...more >>

Hi I have a problem with a VB application that is adding tif images as BLOBs to rows in a table in SQL2000. At present the system is failing as the application cannot insert records to the table. Constantly receiving timeout errors. Images can be retreived from the table with no problems. ...more >>

I've got a specific case where I'm at a loss to figure out which would be better to use: a stored procedure, or a user-defined function. I need a SP/function that will take a few parameters (including PartNumber - a varchar(50) and FormulaCode - a varchar(1) ), and will return a calculated v...more >>

can anyone tell me how to use smtp server as a mail client for sql server notification? how to configure that in sqlagent property? thank you!! (sql2k on nt4) Steve :) ...more >>

Is it possible to search a database for certain text values? More specifically, I have a value that is in some table somewhere in a huge database and I need to find where it is stored. Is there some sort of searching mechanism to allow this without having to open each table? Thanks ...more >>

Hello, I wrote a report in COBOL using embedded SQL. This report opens 5 insensitive cursors (to avoid locking) sometimes one within the other to access data. 5 tables accessed by this report used in the mission critical applications that require high performance and speed for insert, update ...more >>

Hi all, I need to join two tables but the joined table must be displayed only in the first group's row... Table 1 FieldID Value1 Value2 LookupField 1 A B N1 2 C D N1 ...more >>

As I see, the default values from the datepart function results in Calender Year Quarters/Months". Our business deals with Financial Year from July01 Through June30th. The question(s) I have is: 1. Is there a way to change the default setting of a definition of a 'year'? Whereby using st...more >>

Hi there... I have a couple quick questions. I'm making a table where there an be either a numeric value or a text value stored. So I need two columns like this where both are nullable: EvaluationScore tinyint EvaluationComments varchar(1000) My questions are: 1) Does the size of the...more >>

I am using Access 2002 and connecting to SQL Server 2000 using ODBC attached tables. I have to change one of my SQL Server table fields to a BIGINT data type. How am I going to set a variable in Access to handle a bigint field as I think the largest field type is Long. Thanks. David ...more >>

Hello, I'm having a problem with hierarchies. I've got a simple parent child relationship as defined below and I'm wanting to return rows from a specified child to the root node. e.g. if I look at Orange I want three rows returned (with columns of ident and n) --7,Orange --2,Fruit --1...more >>

I'm using Access as a front end to SQL. Our product ID (key field) in the Product Name table sometimes changes. I need all of the associated records in the SQL Order table to change when a product Id is changed. What is the best method to use? VB on an event -- or -- Run a SQL stored pr...more >>

Hi, I've included SQL scripts at the bottom of this message so that you can create the database if you need to. It only has 3 tables. I need help writing the SQL statement that gives me marketing performance data for sales personnel. There are 3 tables: 1. tblEmployee is for Employee da...more >>

I have an existing field which i wish to alter to being primary key. I can't seem to get it right. This is what i'm tumbling with: ALTER TABLE tblFJV_Tilsagnsramme ALTER COLUMN RammeJournalNr nvarchar (10) not null CONSTRAINT jrnnr_pk PRIMARY KEY Thanks in advance. /Snedker...more >>

In my environment we create a new view each month for our data. The views are created by a DTS package which calls a stored procedure. Over time the stored procedure has been updated to add new fields to the view. The problem is that when the new fields were added, the historical views were...more >>

In my application I have several store-shops. Each receipt that is printed in the store needs to have the name of the company, the address of the headquaters, the address of the shop, the phone number, and all of that. Now, I wanted to put all of that in separate table. Now, do I add, in that ...more >>

Hi Freinds, SQL 2000 I detached a db abd copied files to a new server. then trying to attach there. It attached readonly. when I tryo to convert to not read only error 5105 device activation error pops ups. Please advise. Thanks in advance, Pat ...more >>

Ok, Heres a little bg info... I've got one table thats got phone number in this format: AreaCode | Prefix | Suffix -------------------------- xxx | xxx | xxxx And another table that has it in: (xxx) xxx-xxxx. Now I realize joining these two tables on the phn num isnt the ...more >>

Can anyone please help with converting the SQL below, I just can't get real T-SQL to handle the IF part. Thanks. SELECT tbl_Portfolio.PortfolioID, IIf([ReferenceLevel]=1,[ClientRef],[AccountNo]) AS BrokerAccRef FROM tbl_Client INNER JOIN (tbl_Broker INNER JOIN tbl_Portfolio ON tbl_Broker.Bro...more >>

Hi, I know a lot of people went throught this, but now it's my turn and I cannot find anything convinient enough for what we need. We are using installshield to deploy our application and there is a tool in there that generates the entire script to generate your db. BUT of course it does...more >>

Hi, In my installation, I need to check for the SQL Server 2000 installation and if it is not there, I need to install the MSDE 2000. Can anyone please tell me how do I detect the installations of these software in my installation program. Also, I would like to know if my database code wi...more >>

Does anyone know why this string isn't concatenating for me? I know this must be something simple that I am forgetting. declare @test char(2000) set @test='bcp "select ''$$$INS001''' set @test=@test+' queryout ' print @test...more >>

BOL states: "INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement." Why not? David Walker...more >>

For Bulk Insert and BCP, the Tablock option is described as such in Books Online for SQL 2000: "Holding a lock only for the duration of the bulk copy operation reduces lock contention on the table, significantly improving performance." Well, holding a lock only for the duration of whatever...more >>

Hi all, I have a table that contains name, address etc. I want to use the data in this table for a demo and obviously, I don't want real names and addresses to be displayed. Is there a way to swap data fields? For example, I want to swap last names and the city field, Thanks, js ...more >>

Hello, all. We need to copy the results from Query Analyzer into Excel, but when we do, it doesn't show the column names. Excel can connect to the database, but we'd like to run the SELECT in QA, but also get the column names as the first row of the results. Thanks. ...more >>

It has come to our attention that the expected (and observed) behavior of a specific T-SQL statement is not guaranteed. You have probably encountered the issue before, but I will provide two examples here. Syntax Example 1: CREATE TABLE #T1 (T1 int, Z1 varchar(20)) INSERT #T1 VALUES (1...more >>

Please could someone advise me how to determine the row count of the database tables, presumably stored in the system tables? this would avoid me having to use "select count(*) from ..." I have looked in SQL Books online to no avail Thanks...more >>

Hi, My sql server instances are named as Server\Instance e.g srv1.abc.corp.com\PSE1 I'm not able to connect to such server instances using the SQL express manager 2005. However, there is no such problems in connecting to non-named (default) instances. srv.abc.corp.com Any suggestions on h...more >>

Hi All, Is there any way to find all the stored procedures call from one stored procedure. For Example, The Main sp is sp_Main. Inside this sp there are 5 sps called, named sp_Sub1, sp_Sub2, sp_Sub3, sp_Sub4, sp_Sub5 and Inside sp_Sub1 there is another function called fn_test1. Giv...more >>

Well, I'm getting better at this thank's to all your help, but i've got another question. I need a little help with constructing a Cross Tab Query. I'll start with the table: CREATE TABLE [dbo].[crt] ( [crtCsiID] [int] NULL , [crtRow] [smallint] NULL , [crtCol] [smallint] NULL , [crtChr...more >>

All tables belong to Primary filegroup by default. Are there any SQL commends to list tables in a certain filegroup? Thanks in advance, ...more >>

Hi, This may seams a very simple (stupid) problem, but the point is that I could'n solve it yet. So I'd appreciate your help. There it is... I'd like to test a table A for a specifi row, and if (the row) exists then it should be updated, otherwise it should be created. Something like: b...more >>

I want to execute a stored proc. multiple times with the least amount of code. For example, I want to execute: "sp_delete_maintenance_plan @plan_id = xxx" from the following list: "SELECT plan_id FROM msdb.dbo.sysdbmaintplans WHERE plan_id <>'00000000-0000-0000-0000-000000000000' GO" Is ...more >>

Is it possible to set the starting row in an order by statement? For example, Select * from Accounts order by name where name like 'B%' The result of this query, which currently is not correct, will list all the entries in the Accounts table and set the starting row at B instead of A or Z....more >>

I have two tables that I have to join together. The first table contains what will be phone numbers and a date. The second table will contain rates that the phone number needs to be charged. Here is the first table (Phone Numbers): CREATE TABLE [PhoneCalls] ( [CallOriginatingDate] ...more >>

i hav a datetime field and i need to know the day of the year the date represets ie 2004-12-20 00:00:00 = 355 ...more >>

hi all! I have to find ou t whether a record is duplicated in a table.. I know ia have to join with the same table using an alias but i can resolve it... I have to delete the duplicated records ... any idea? thanks!...more >>

Hi: I have a DTS package that first brings in data from another database into SQL Server. In the source database, the database tables are like this: TableName20041014 for example. At any one time, 10 of these tables are in the source database, every day the oldest one is deleted, i.e.: ...more >>

we have a dotnet webpage querying a 64bit sql table, the page will query the DB for every row produced (ed table in a table) although the statement If SqlConnection1.State = ConnectionState.Closed Then SqlConnection1.Open() End If it will always open a new connection and...more >>

Hi: When working in Query Analyzer, I was working executing different code in different batches. I noticed that if I executed the same batch twice in a row, the second time it would complete immediately (and say it was successful), but it wouldn't actually do anything. The same would happen i...more >>

we a system fed by various sources (foxpro,dotnet,ado) and we are seeing delays in the data being available, there are no insert errors, but the data does not appear for selection for at least 5 minutes ...more >>

I've written an ASP app that requires users to login - normal stuff. As is typical with users, sometimes they just close the browser which teminates the app. What I've done is created a new table in SQL that inserts the date & time when a user logs in. All is well as other stuff. If the user...more >>

Hello All, We know that both insensitive and fast-forward cursors do not reflect changes in the recordset if the base tables data changes. But, can someone please explain what the exact differences between the two are? Cheers! ...more >>

Does anyone know what the theoretical maximum and minimum values are for UNQIUEIDENTIFER fields. A string of '00000000-0000-0000-0000-000000000000' can be converted to a GUID value, and it seems to work as a minimum, but my attempts to use 'ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZZZZZ' as a maximum valu...more >>

Hi, I've seen two different ways to feed SQL Server a random seed: (1) SELECT RAND( CONVERT( Int, CONVERT( VarBinary, NEWID() ) ) ) (2) From BOL: SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) It ...more >>

Hi, What are the possible enhancements one can make in stored procedures. At the moment I'm using SPs to add/edit/delete and select purposes. We develop web applications. I'm a database developer. I want to create general purpose stored procedures which can be used by web developers for ...more >>