Hi I have a table 'contacts' that contains following columns: fistname middlename lastname contactno i wrote following query which is not accepted SELECT (firstname + ' ' + middlename + ' ' + lastname) AS Name, contactno AS Mobile FROM Contacts WHERE Name LIKE 'A%' ORDER BY Name It ...more >>

My database is being marked as "Suspect" and I think it's some time after I change a column from data type smalldatetime to datetime and reboot. Does that make sense and what can I do about it? ...more >>

Hi, I need your kind help in writting a stored proc. Please go through the below piece of code: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[up_file_split_uk] ( @v_totaldipath nvarchar(100), @v_totaldiname nvarchar(100), @v_pilotdbinstancename nvarchar(1...more >>

I've been request to programmatically remove offset records from a dataset. I've been working on this for a few days now and have not been able to determine if this would even be possible. Could I get assistance with the query. DROP TABLE #Temp CREATE TABLE #Temp (RecordID int IDENTITY(1,1)...more >>

Hi, is there anyway of preventing NULL values in a db, what causes them is it the field type etc? I have noticed that not all fields contain a NULL value others are left blank no problem at all but I need to resolve the issue. Regards Simon -- Simon Gare The Gare Group Limited w...more >>

I am using SQL2005. I have a table with an IDENTITY column like so: CREATE TABLE myTable( ID int IDENTITY(1,1), ColA varchar(50) NULL ) I need to create a stored procedure that will insert data into that table with the value of ColA = {acco...more >>

Hi, I try to play with the index on Northwind table and I created two tables called Customers1 and Customers2 both have the exact same number of records (1000). I created on index on Customers1 for CompanyName, ContactTitle, and Address. on Customers2, I created three different indexes on...more >>

SMO Restore API seems to require the database name to work. This won't work without the database name. Restore restore = new Restore(); restore.Action = RestoreActionType.Database; // BUG: {"Set property Database to accomplish this action."} //restore.Database = databaseName; restore.Rep...more >>

I have a web app that creates a list of items ordered. Server is Windows 2003 and SQL Server 2000. The code: declare detcsr cursor for select orders.orderid, orderdetail.productid, orderdetail.productname, orderdetail.quantity, orderdetail.unitcost from orders inner join orderde...more >>

replicated. We are in the process of updating our website from coldfusion (which is on one server) to another database (no another server in another state), How can I make sure that when the changes are made on the old site that the same changes are repreduced identically on the othere servers...more >>

Hi There, I'm using SQL Server 2000 and I have Vendor Table with Name Colum That hold LastName and FirstName and I want to seperate this into two columns one for FirstName and the other Last Name I used Excel like function SELECT LEFT(Name,LEN(A1)-SEARCH(" ",Name)) from Vendor And SELECT RIG...more >>

Hi there, I'm using sql 2000 and I have Vendor table with Name column that hole Last Name and First Name like Frank Glenn I want to create a 2 columns one for first name and one from lastname I found Excel function look like this SELECT LEFT(Name,LEN(A1)-SEARCH(" ",Name)) from Vendor ...more >>

If I want to do a one line test to see if an alert exists, it's as easy as if exists(select name, enabled from msdb.dbo.sysalerts where name like 'Access to database denied%' and enabled = 1) blah blah blah However, if I want to test if a notification exists (preferably in one line), I'm n...more >>

Hi, My problem is how the following can be solved as easy as possible by using (t)sql: I've got a tabe with visits: clientID, beginDateTime, endDateTime 1, 2007-08-01 10:00, 2007-08-01 10:30 1, 2007-08-01 10:30, 2007-08-01 10:35 1, 2007-08-01 10:35, 2007-08-01 11:00 1, 2007-08-01 11:00,...more >>

I have a SP that has only one parameter. I want to add another parameter but make it optional so I can use it with some newer code and not affect the old code. For example, I have the following: ************************************* CREATE PROCEDURE DisplayJobHistory ( @UserID int ) ...more >>

I am trying to update a table with values from another. When I run the following script: select potran.projectid, potran.lineref, aptran.polineref, potran.ponbr from potran inner join aptran on potran.ponbr = aptran.ponbr and potran.lineref = aptran.polineref I get the results I expect...more >>

[Communications failed before mirroring was fully started] I've got our mirror environment setup completely scripted. 6 databases all on the same server...all mirrored to the same mirror server and witnessed by the same witness. 5 work flawlessly. Just this one seems to keep causing this....more >>

I have a table of historical (daily close) stock prices, can I calculate the moving average in a simple sql statement? Or would i need to write somethign to calc and insert vals for 20, 50 , 200, etc day moving averages and store them per row? since it is a moving average, the average will...more >>

We have a SQLServer 2005 installtion and I need to monitor one particular remote database very closely for any read-write activity. We are trying to spot any unauthorised access - it supports a web application. Is there a way of setting up sqlserver so that it sends out a simple email whenev...more >>

i have a table structure like following. i need to update action_date to be same as the closest action_date of action_code='S' for each email address (show as desired column). email_address action_code action_date desired column xxx@yyy.com S 2007/06/27 2007/06/27 xxx@yyy....more >>

Dear all i m using sql server 2000, i have folowing sample data set CREATE TABLE X (C Varchar(10) NOT NULL PRIMARY KEY) insert X select '100-07-001' union all select '100-07-002' union all select '100-07-003' union all select '100-07-005' union all select '100-07-007' union all selec...more >>

I call sp2 from sp1. If there is an error in sp2 - I return the error to sp1 and rollback the transaction. But that is not happening. Here is the scenario: CREATE TABLE tbl1 (fld1 varchar(20) NOT null, fld2 varchar(20) NOT null) --------------------------------------- CREATE PROCEDUR...more >>

I hope I have selected the appropriate place to post this question. I have a curious issue with a DELETE statement - we are baffled by the results we are seeing, so I wanted to post here and see if anybody has any ideas of what is happening with this. Here's the scenario: 1) Table st...more >>

I am used to using the DTS package in sql server 2000 to export files to users in a pipe delimited format. I now have sql server 2005 management studio and would like to know how to export text files in a piped delimited format to users? Also can you tell me how I can import files into sql...more >>

Hi, i am working on application which will execute sql command on sql2005 I made sqlcommand with text BEGIN TRAN; UPDATE table SET something=valueofsomething where col=1 OR col=3 OR col= 45 .... OR col=5312; COMMIT RAN mine question is how long can command text be? tnx ...more >>

Hi folks, I am creating an external database for a legacy application and one of the requirements is that every table has to be declared twice in the database: TABLE_READ and TABLE_WRITE. Now the idea is that as soon as a change is made to TABLE_WRITE, these changes should be reflected i...more >>

Hi, Is there a feature already in DTS which stops more than 1 DTS from running at any one time? e.g. If a second one is run whilst the first one is running it either exits or waits until the first one is completed. In the DTS properties I have found what looks like the thing I am looking for b...more >>

Hi All - Would appreciate some help with this, i've got an MS SQL database with a table called property, within the table there is a field called photoone, this is Nvarchar and stores the mapped location of photo one. However uploading photo one is optionial and therefore i want to store a ...more >>

Hi, everyone here. I have about 100GB log files and I need parse these log files and append these data to a existing table. There are about 1 billion records contained in these log files. I hope you guys tell me how to efficiently append the 1 billion records to a existing table in SQL Server...more >>

I need to create a query that returns all the columns from a table plus a calculated column. The table being queried has a Varchar(14) column that contains codes that are either 1) a mix of alpha and numeric characters, or 2) purely numeric characters. When the value is a mix of alpha/...more >>

Hello folks, I am writing some applications that get data from a J.D.Edwards (DB2) database. So there is a tremendous amount of truncating tables and importing new data from the DB2. What consequences does this have? What maintenance do I need to do before and after imports? Anybod...more >>

Hey, I have a query executed over a view that contains several joins and unions, and I get the following error: Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded. Microsoft support published Fix for it (http://supp...more >>

I have a 3 digit string, which may be made up of numbers or other characters. What I need to do is write some SQL that checks each character from the left to the right of the string, and if the character is a number then keep it, if it is not then lose it, and finish reading from the string at ...more >>

Hi, I have created a database with two filegroups called FG_GroupData, FG_GroupHistory. FG_GroupData is set as default. FG_GroupData contains two secondary data files i.e. GroupData1.ndf and GroupData2.ndf I can create a table so that it is stored in FG_GroupHistory. i.e. CREATE T...more >>

All, Could anyone help me in writing Best Fit SQL statement. Suppose we have table t1 with coloumn t1 (text) with following rows. 98456 98457 9845 9846 984 985 98 99 and if I query on 98456 the result must be 98456, However if I query on 98455 the result must be 9845 and If I que...more >>

Hi, I am using ADO to connect to SQL Server Express but could not connect to it. Can somebody point to me what I am missing here ? Here is the source code of the program: #import "msado21.tlb" rename("EOF", "EndOfFile") #import "msadox.dll" no_namespace int _tmain(int argc, _TCHAR* ar...more >>

Hi, I'm looking for a good way to preserve child records in a one-to-one relationship. I want to prevent child records from being deleted independently. They should only be deleted through a cascade from the parent record. Here's my example... --Begin script --Create database CREA...more >>

Hi, Hope this is the right forum. I have written an SQL script to migrate = attributes from an existing table to a new table, and then remove the = attributes from the old table. It looks like this: if NEWTABLE does not exist begin print 'Create NEWTABLE' Create NEWTABLE ...more >>

I'm a VB6.0 programmer who is new to .Net. I'm having to write a program in .Net that will call various .rdls in a windows application. I've done the reports in the Business Intelligence Studio (2005) pulling from a SQL Server 2005 database, but I don't have .Net 2005, only Visual Studio 2003 ...more >>