Hi need a stored procedure to replace the 4 commands listed below UPDATE dbo.booking_form SET total_cost = mileage_charge + waiting_charge + CarParkToDriver UPDATE dbo.booking_form SET VAT = total_cost * 17.5/100 UPDATE dbo.booking_form SET GrandTotal = total_cost + VAT UPDATE dbo.booking_...more >>

I am having difficulty in designing my SQL. :( In this setup, an Invoice can have multiple Bills (installment payments). I would like a query that returns invoices with overdue bills a bill is overdue if :: NOT B_Paid and Now() > B_DueDate --- how to put this in the following '???' ...more >>

I want to prevent duplicates in a SQL Server DB table using CHECK CONSTRAINTS. The examples shown in BOL are somewhat like this CHECK (min_lvl >= 11) or CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][...more >>

I want to select only the first n records from a database using VB.NET. I have declared a DataTable and OleDB.OleDbDataAdapter as follows: Dim linkstable As New DataTable Dim linksadapter As New OleDb.OleDbDataAdapter("SELECT * FROM worldnews ORDER BY updated", System.Configuration.Configu...more >>

Tried distinct however does not work to eliminate repeat output of department name. Only want department name to print once with all members then next department name and members etc. select (cccb_name) as 'cccb', CONCAT(member_fname,' ',member_lname) as 'member ' from member, cccb, member_...more >>

When I create a new stored procedure it is created as a system type stored procedure instead of a user type. Can someone tell me how to force it to be created as type user instead of system? Thanks in advance. ...more >>

Environment is SQL Server 2000. I run the following very simple query against 3 tables from the first SQL server. One table "LOCTAB" resides in the same first server. Two other tables reside in another SQL 2000 Server but linked to the first one. SELECT PD.FLD1, P.FLD2, PD.FLD3, PD.FLD4 F...more >>

I am trying to use the GUI to assign a user without a login to a role. The user was correctly loaded without a login, and the grnerated script shous that >CREATE USER [CertUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[cert_datareader] When I go to the management studio and select properties fo...more >>

Over time I have populated a database with entries in two tables where a HEADER table exists in a one to many relationship with a DETAILS table. The DETAILS table has one field that links records in it to the primary key values in the HEADER table. In practice there must be at least one record...more >>

(script included at end) I have two tables where TABLE1 has a nullable column that sometimes has an ID from TABLE2. (Simlar to having two users sharing 1 address record where sometimes a user has no address record) In the queries below I am trying to locate orphan records in TABLE2 (That is...more >>

I'm sure there is some implicit conversion that is causing this, but after hours of trying every possible cast/conversion I'm turning to the community for some help. Here's the query from a stored procedure: select distinct(cit_id) from contentitems,itempropertyvalues where categoryid ...more >>

Hi, In SQL 2005, we can create a maintenance plan for database. I have created a such maintenance plan and i wanted to deploy the same maintenance plan to customer, so that cusomter is not required to spend the time for creating the maintenance plan. But unfortunately, i am not able to fin...more >>

Using the view (select) below, can i access the table named People if it resides on a different server and a different database name? For example, the table named People is being moved to a server named DATABASE but I still want to be able to join between the 2. Is that possible, and if so, ...more >>

Does anyone know how to remove the date / time stamp from the back up file name created? I have a maintenance plan the keeps adding the date / time to the file name and I'd like to remove it. Any help would be appreciated. Thanks! Rick ...more >>

I am new to SQL Server programming and have a bit of a task to handle. I have a new table that I want to populate with increasing integers. I want to be able to say the minimum and maximun number and they have to be increasing. I am looking to end up with something like this: NUMBER 1000 ...more >>

can a foreign key speed up data retrieval? i have 2 tables - parent and child - that are joined in a query and they did not have foreign key between them but did have indexes on the join columns. i know the foreign key prevents bad data but would it also help out the query? ...more >>

System is SQL 2000 SP4. I have a processing need to go through many databases that have the same structutre. As I loop through and get each databases name, I'd like to put this in a variable and then use this in a SELECT statement. I can't seem to get this to work. Is this not possible...more >>

An DB table has the following columns: ClassID - int TeacherID - int VenueID - int DateAvail - date/time StartTime - date/time EndTime - date/time Seats - int Each teacher is supposed to teach a class at a given date, time & venue. Now it's not possible for a teacher to each multiple cl...more >>

i want to move updates and deletes and inserts from my vb code to stored procedures. my question is.... is it better practice and performance to create separate procedures for each action (one for inserts, one for deletes, and one for updates) or is it ok to combine them all in one procedure an...more >>

Right now I hate in my T-SQL proc 3 definitions I need to fill with data that is from a single row DECLARE @UsersFailedAttempts int DECLARE @UserLastLoggedIn datetime DECLARE @UserLastLoggedOut datetime right now i hate to do 3 sets on this! to set each of those, is there a faste...more >>

I need to update 520 records in sql table name emdet the field in this table is det_email_ada I need this field to be populated with the user's firstname and thier lastname (which are held in this table) and then place all of it in det_email_ada like following firstname.lastname@shellharbou...more >>

Hi group, CAn anyone recommend best practices with this situation, which I hope is somewhat common. Say I have a table called Person and a table called Company. Person has a CompanyID column that is a foreign key to the PK in the Company table. I want to define this as a foreign key in t...more >>

Hi I'm new to working with SQL and I want to know how to write a script to: 1. check the identity of all the tables in my db 2. return the results to a table with the table name in one column and the Identity status in another. I don't think its too hard, but I haven't a clue. I just had th...more >>

Hello I need to create a varchar column in a table and restrict it to be unique but only if entered , meaning it can accepts null but if user enter data into it then this data has to be unique compared to other non null data in that column how can i create unique index or constraint or c...more >>

Hi, I have a huge table which has about 300 columns. I have to create an update trigger to log the data change for this table. Is there anyway I can tell what column and the column name change without writing all the If update(column_name) statements. Thanks Ed...more >>

Hi All, I have the table and the values are defined as below: Id PuringInterval Description 1 10 UTM 2 20 Hourly 3 25 Daily And I would like to have the select or the view statement to return the following UTM Hourly Daily 10 20 25 So I want to convert from rows to colu...more >>

Hello I remember when working with MS Access years ago there was a possibility to use First and Last in a Group By statement that was helpful in some cases , for example Select ItemID , Last(SalesPrice) AS LastSalesPrice , Sum(Qty) AS TotalQtySold From Inventory Where ActionID=1 ...more >>

Hi everyone. I need to look differences between using GUID and ID as key of my tables. I have two tables without primary key on them. Both have 100 000 records and the varchar value is full (6000 characters) CREATE TABLE TableInt (id INT IDENTITY(1,1), column1 VARCHAR(6000) NOT NULL DEFAUL...more >>

Hi, I have a instead of trigger that has a sample code as the following CREATE TRIGGER EDGES_IO_EDGES_Case_Detail_Table_TR /* trigger name */ ON EDGES_Case_Detail_Table /* table being audited*/ INSTEAD OF UPDATE /* action on table record...more >>

Hi Can someone help me? I have got a Ado Connection with following provider string to my local database: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=LOCALDATABASE;Data Source=localhost; Use Procedure for Prepare=0;Auto Translate=True;Packet S...more >>

Hello Everybody, I have one table in my DB which has around 10 millions rows. It has one column which is varchar(1000) Now we may get foreing character for the value of that column so I have to change its datatype to nvarchar(1000). So my question is, If I alter table to change that column ...more >>

Hi, I'm trying to figure out the best way to convert XML to rows and insert it into a table. I've tried XMLDocument in C# and it's way too slow and resource intensive. I've tried the XMLREADER in C# and it's pretty fast, but takes a little more work and requires many calls to a stored proced...more >>

I am trying to solve for Time between calls in Calls table set up as below: CallDate CallTime CallLength 5/7/2007 1:19:00 12:00:09 5/7/2007 1:19:00 12:00:35 5/7/2007 1:53:00 12:10:28 5/7/2007 2:32:00 12:34:11 What makes it even trickier is ...more >>

i am trying to develop the same as job activity monitor tool and using SMO and C# displaying it in a webform but it takes more than 30 min to display the same.here is the code. string strconnect = System.Configuration.ConfigurationSettings.AppSettings["ConnectStr"].ToString(); Sq...more >>

I need to find the longest string in a column. What I am doing is searching for names in email addresses. We are trying to infer gender. So I might have the following email address. AlexandraVonStoddard@gmail.com. This email address has the names Alex, Alexandra, and Todd in it. I want to retu...more >>

I have a table Financial_Values that has the following columns: Year(pk), Month (pk), Account_No (pk), Amount The combination year, month & account no varies for each year & month. I need to create sp or function that creates a result set that has the follo...more >>

we have customers running sql 2000 and sql 2005. I need to create some indexes on views. I know that not all versions of 2000 allow that but it seems any version of 2005 allow it. is there some sql command that i can run to see if the version allows the creation of indexes on views. Darin...more >>

Hi, I have a simple problem. Create table T ( ColA Char(2), ColB VarChar(20) ) In ColA, there are always be value in (A, B, C, D) I want to create a check constraint on ColB, If ColA = A Then Len(ColB) = 10 ColA = B Then Len(ColB) = 12 ColA = C Then Len(ColB) = 15 ColA = D Then L...more >>

I am very new to SQL, and only use it very limitedly, so apologies ahead of time. I have a website that inserts data into a database, using the code below. Basically, I've got one INSERT INTO statement that inserts one line of data into one database, and and then a php for loop that inserts ...more >>

Hi all, I've a simple table T with N1, N2 cols of varchar(255). The table has n=20 rows (more or less), ie ----------------- N1 N2 ----------------- COL1 VAL1 ... COLn VALn What Is the best way to obtain a swapped table ST ? ---------------------------------- COL1........more >>

Hi, How we can find length of text datatype value. Create Table t ( Col Text ) Insert into t Values('ABCD') Select Len(Col) From t --Not Working Select DataLength(Col) From t --Return Null Rahul ...more >>