Hi, due to special reasons I have to ensure, that in insert and update statements for varchar-columns (which allow NULL-values) the value '' automatically becomes replaced by NULL before the records have been inserted/updated. (This because I have to convert a large application from anoth...more >>

I have read that the value of @@DBTS changes on every insert and update. However I am finding that that is not true. I can do multiple inserts of rows into a table, and all of the rows will have the same value in the varbinary(8) column that is populated with the value of @@DBTS at the time of ...more >>

I have a table : CategoryID int SettingName nchar(50) SettingValue nchar(500) CategoryID can be 1,2,3,4,5, .... for CategoryID = 1 : rows exist for every possible SettingName for CategoryID = 2,3,4,5 : a row might exist for any given SettingName ... or it might not I need a sproc ...more >>

I have these results: ID Worked 1 25 2 30 5 20 8 32 9 27 10 26 ID Spent 1 35 2 24 3 22 4 50 5 30 6 28 Required result: ID Worked Spent 1 25 35 2 30 24 3 0 22 4 0 50 5 20 30 6 0 28 8 32 0 9 27 0 10 26 0 I think I can make it but it will be complicated, a...more >>

I have a view that includes a left outer join. I want it to be indexed because it could easily have over a million rows in it. At any one time we are bringing back less than 1000. Adding an index complains because there is no clustered index. Adding the "clustered" keyword fails with "Cannot...more >>

On a somewhat theoretical note, I'm curious what properties others might think a Brand might have (or, as noted by Alex P. in response to my earlier "Simple primary key question", most folks refer to car Brands as "Makes"). It would seem to me that all aspects of the Brand are related to the M...more >>

Hi, I'm building a SQL query that will delete all records within two different tables, the records in both tables belong to the same user with a userID as the unique identifier. Can this be done with one DELETE statement? Thanks, Nino ...more >>

Hi, I need to delete information within two different tables that has the same unique identifier relative to a single user (USERID). Basically I have to remove any records for this user from both tables with one DELETE statement. Any suggestions would be great. Thanks, Nino ...more >>

If my database is in simple mode, I think default is clean transaction log after commit successful correct? I don't think I need to run following statement to clearn transaction log correct? If I guess correctly, the following option is only useful in FULL mode. sp_dbopti...more >>

Hi all, Is there a way to convert an XML document returned by a "FOR XML" SELECT query back to CHAR or VARCHAR or TEXT? I am trying to create a reusable stored procedure returning an XML fragment. I know it is possible to insert a string as it is into another XML document using !XMLTEXT dir...more >>

CTE's appear to have 2 advantages: 1. Compliance with ANSI 2. Recursive queries. Aside from these reasons, and perhaps a syntax preference by some, I do not see a good reason to go out of my way to use them. The remaining situations don't seem to give any benefit: 1. If I wanted to use...more >>

I have created a table with a field which is defined as a datetime field. I set the value for default to be (0). When I try import data, I get an error message: "Destination does not allow NULL on column pair 37........." Now perhaps it should be stated that then enitire column in the source ...more >>

Hi All, I have one doubt, if any one can help-me: I have one SQL(db) that receive a lot of inserts, and I need get IDENTITY value of current insert, but the machine have 4 processors. If I use SCOPE_IDENTITY() I will get the corret value? Thanks. ...more >>

hello, I have MFC VC++7.1 program which has the following code segment: // Specify the colums and format to copy retcode = bcp_columns(hdbc1, 6 ); if ( (retcode != SUCCEED) ) { return; } retcode = bcp_colfmt(hdbc1, 1, SQLCHARACTER, 0, SQL_VARLEN_DATA, (unsigned char*)"|", 1, 1); retco...more >>

I'm working with SQL Server and I'd like to run a query to find out a list of the user stored procedures in a given database, and what the parameters for each stored procedure are. ...more >>

I wrote a stored proc that results in a dynamic rowset of different columns. (not predefined except ID). To Make it available for a join with i.e. a view, I want to use an inline-tablereturn function, because this kind of function doesnt need a predefined Tabledefinition, too. But: I have jus...more >>

Hi, I have an application that's running fine on development servers (web and database-sql server 2000). I'm updating a record through a third party component but I don't think the component is the problem. What's happening is that I'm updating fields that are part of view. I'm only updatin...more >>

Hello! I need your help in developing a procedure which is being called by the asp .net web page when the user insert into 5 text boxes phonenumber in the format xxx-xxx-xxxx. The table in the database has id for the phonenumber, and a field called Phone-number which takes just one phone numb...more >>

I have 2 reports in my Access program(SQL DB) that I need to export into 1 text file. The OutputTo function works great for 1 file, but I can't figure out an easy way to add the second report to the end of this text file....more >>

hey all, i was wondering about an invoice system that contains many customers, how are the invoice numbers normally assigned: does each customer get its own sequence of invoice numbers? thanks, rodchar...more >>

Is it possible to use ntext or text datatype for CLR C# stored procedure input or output pararmeter? in SQL 2005 Thanks...more >>

Hi, I have a problem which is hard to describe but I will try. I have table that has a relationship one to many with another table and it has a relationship many to many inside it self. Each record had a field that is called fparent and fparent can relate to the primary field fcomponet. ...more >>

Thanks in Advance, Culam id phone_alternate phone_home phone_work phone_cell ----- ---------------- --------------- ------------------ -------------- 47 NULL 619 222-2552 NULL NULL 47 NULL NULL 61...more >>

Hello all, i'm going to be adding a new column (1 byte) that will become a non-unique indexed value into a table that i'm adding records into. i know for a fact that this table will grow from the current thousands to millions of records over the span of a few weeks and have allocated the datab...more >>

Hi All, I have a bit of a delema here, that I know someone knows how to do it properly. If the value of DSP_IN_HOUSE = 1, I need the where clause to say: where DSP_IN_HOUSE_IN = F.CollectorDesc the code I am using is: left outer join tlkCollector F on case when DSP_IN_HOUSE = 1 then DSP_...more >>

I tried creating the following procedure but it's giving me error. Can someone tell me how can I get this done? Thanks, Alpha USE VMS GO CREATE PROCEDURE SavePrevOdo AS Update VehDetail set PrevOdometerDate = v2.LastOdometerDate, PrevOdometerReading = v2.LastOdometerReading fr...more >>

I have a test database that needs to store different types of test data: boolean, integer, and double. Any opinions on use of three columns per row (char(1) (nullable), int, float) versus a single sql_variant column? The table will rapidly have a billion rows, so space is a concern. Thanks, ...more >>

Hi There, I'm having problem creating the following output: Fact Table 1 (Sales) ----- Date_Key smallint, Emp_Key smallint, SalesNbr smallint, SalesAmnt money Fact Table 2 (Attendance) ----- Date_Key smallint, Emp_Key smallint, ScheduledTime int, Attendance int Dim Table #1 (Da...more >>

Hello, I work for a discrete event simulation company. Much of data created by our simulation tools can be represented like the following: EventID, StartDate, FinishDate, Replication So, If i was simulating a hospital, the table might look like the following: (Hopefully shows up decently ...more >>

I have two tables that are related - Region and Office CREATE TABLE [dbo].[Region] ( [RegionCd] [varchar] (3) , [RegionDesc] [varchar] (50)) GO CREATE TABLE [dbo].[Office] ( [OfficeId] [int] NOT NULL , [OfficeName] [varchar] (100), [RegionCd] [varchar] (3)) GO The SQL statement ...more >>

i have the following vb code that i want to turn into a stored procedure. Can it be done without using cursors? thanks for any help! what this code does is it says for each item, which other items reference it in the column called source. Set rst = CurrentDb.OpenRecordset("SELECT [...more >>

Hi-- Aggregate function MAX is confounding my efforts? I really need to know why the subquery won't return 1 of the rows contain percentagetotal 88.00. SELECT STUDENTID, GRADEDTS, CETESTID, PERCENTAGETOTAL FROM ZZZ_DUMMYTESTS T WHERE PERCENTAGETOTAL = (SELECT MAX(PERCENTAGETOTAL) FROM Z...more >>

ni need to convert a date given as declare @thsDate as datetime set @thsDate = getdate() to an integer of YYYYMMDD thanks kes -- thanks (as always) some day i''m gona pay this forum back for all the help i''m getting kes...more >>

Ok, if you run the following simple piece of code: -------------------------- CREATE TABLE #Temp (Record_Date DATETIME ) INSERT #Temp VALUES('2005-06-29 12:17:48.140') --CREATE INDEX [IX_Record_Date] ON #Temp (Record_Date) DECLARE @Begin_Time AS SMALLDATETIME DECLARE @End_Time AS SMAL...more >>

hey all, I'm trying to convert my Access query so it will run on SQL Server but I'm having problem with syntax differences: UPDATE Transactions, InvoiceHeader SET Transactions.InvoiceID = [InvoiceHeader].[InvoiceID] WHERE (([Transactions].[CustomerID]=[InvoiceHeader].[CustomerID])); ...more >>

We have a SQL Server 2000 transactional business production system that is installed at 20 different locations and has been running very well in all but 1 location that was installed over the weekend. The hardware setup "seems" to be rock solid according to the MS SQL Server performance guidelin...more >>

Hi all, I've got a difficult problem I'm trying to work out in SQL. I've got a set of data (see below) and I'm trying to choose records that match up to a target value. The issue is the sum of the records selected to match the target value must also match a target date, using a weighted date ...more >>

I want to retrieve the SQL Server’s database schema (ex: pubs) to WORD application to create a system design document, is any good suggestion for this? ...more >>

I have two machines, both have SQL Server 2000 installed. A's IP is 192.168.0.1, B's IP is 192.168.0.2. How do i use SELECT statement to SELECT a table's content from B machine to A machine's database?...more >>

Hello, I would like to make a procedure of sorts that accepts as input a full sql statment and then is able to return a list (or print) of only the tables referenced in the sql statement. Is this kind of code available? Thanks. Ben...more >>

Hi I am looking at getting users sending there static Sql Queries to a sql mail box to retrieve there queries.Sql mail is working fine but I have done something wrong on the query. I have set this up below. sp_processmail @subject = 'sqlquery', @dbuse ='pubs', @separator =' ' , @filetype...more >>

I have the following keys in Consumption: - Plant - Material - Month - Year The above are the primary keys in the table and the following are non-key fields: - Quantity - Amount I have data stored in this table currently but many times I get feeds which are stored in the table: ...more >>

I have a baffiling problem trying to run a DTS from a VB6 application. The application is called from an event on an FTP server, but always hangs at the execute command for the DTS. If i reun that application as a stand alone exe its fine..... Im now wondering if i can get around this problem...more >>

Dear fellow sufferers... I get the error noticed in the subjectline, while trying to put a 150MB ZipFile into a SQL-2000-SP4-DB. This is the way I do it: '************************************************************************************************** Public Function CYCLE__GisData_ZI...more >>

Can somebody help me, I need to know what Index.rebuild does. Also the different types of log files in Sql Server. Please help, Joe....more >>

hi friends, I want to check the relationship between tables before migration. so i wrote a procedure which will push the unrelated data from the source db(@i_oldDB) to the error database(@i_errorDb). alter procedure TransactionValidation ( @i_oldDb varchar(100), @i_errorDb varchar(100)...more >>

Hi, I am facing a problem. I have a table with indexes (integers). These occur out of sequence i.e. not in serial order. Due to this the performance has decreased. I have tried the following commands but of no help : 1) dbcc checkdb 2) dbcc dbreindex 3) dbcc indexdefrag I...more >>

Dear folks, I am facing up hindrances when I move a table from one server to another one which contains 'text' fields. Sometimes I obtain errors such as: "field too large" or something like that. How do I work out this? Putting on the destination table 'varchar(5000)' instead of text 16...more >>

I use the DTS Import Wizard to copy one database to another. I notice that it does the importing of tables and data alphabetically. This causes some problems for me because I need some tables to be copied in certain order due to the relationships and constraints. Is there a way for me to us...more >>

Here's a fun question :) If I have an instead of trigger on a table, which replaces the insert, how can I get the identity insert from the data inserted? --- example --- IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NU...more >>

Hi, Im tring to create a stored procedure with objective as 1) add new project details 2) create table with name as <projectcode>_MONTHSETTINGS pls correct my below code thanks in advance -DNK ------------------------------------------------------ CREATE PROCEDURE [dbo].[AddNewPr...more >>

Hi, We are migrating from DB2 to SQL Server 2000. We have used DB2 OLAP functions (rank, sum, etc.) quit liberally. How should be translate them in SQL Server. Thanks Prashant ...more >>

Hi, I was hoping someone could help me... I have a INSTEAD OF UPDATE trigger set up against a view: CREATE TRIGGER mytrigger ON mytableview INSTEAD OF UPDATE AS UPDATE mytable SET field1 = myfunction(inserted.field1), field2 = myfunction(inserted.field2), field3...more >>

Hi I have declared a text field in a function and cannot seem to update it: DECLARE @tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description] VARCHAR(355), RowId INT IDENTITY(1, 1)) ........ UPDATE @tblOutput SET Imports = CAST(Imports AS TEXT) + CHAR(11) I get the error - Error 403: I...more >>