How To Perform An Update To A Collumn With Two Substrings
Mar 22, 2005
I want to do the following in an update:
Update [table]
Set collumnName = Substring(collumnName, 1, 2) = '11' AND Substring(collumnName, 3, 5) = '00000'
Where (conditional statement)
SQL is not letting me perform the update because of the AND statement between the two Substring's. Is there anyway around this...is there anything else I can do to acomplish what I'm trying to do.
Thanks in advance everyone.
RB
View 5 Replies
ADVERTISEMENT
Jun 6, 2008
Hi all, I hope you guys can help me with the following bit of T-SQL. I already have a solution but I really don't like it and I've been trying to find a simpler more elegant way of doing the same thing.
Firstly, let me present you with a brief explanation of what I am trying to do together with some sample data for you to play with and hopefully assist me in finding a better solution than the one I’ve come up with.
So.. here goes
I have two tables:
create table #VehMake (VehMakeId int, VehMake varchar(100))
insert into #VehMake
select 222, 'FORD' union all
select 210, 'FORD (USA)' union all
select 223, 'FORD (AUS)' union all
select 269, 'HONDA' union all
select 253, 'NISSAN' union all
select 280, 'VOLKSWAGEN'
This contains various vehicle makes which I'm sure you'll recognise!
The second table contains vehicle histories:
create table #VehicleHistory (PersonId int, VehMakeVehModel varchar(200), VehMake varchar(100), VehModel varchar(100))
insert into #VehicleHistory (PersonId, VehMakeVehModel)
select 1, 'FORD (USA) MUSTANG' union all
select 2, 'HONDA CIVIC' union all
select 3, 'NISAAN ALMERA' union all
select 4, 'VOLKSWAGEN PASSAT'
As you can see, in the second table, the second column contains a string of the vehicle Make and Model in one string. What I need to do is to split the Make and Model in to separate columns with an update statement.
This seems easy enough with a simple LIKE comparison:
VehMakeVehModel like VehMake+' %'
....BUT if you notice, there are two records in the #VehMake table that are similar but not the same. These are the 'FORD (USA)' and 'FORD (AUS)'. The update statement would return two records from the #VehMake table when trying to match with the first record in my #VehicleHistory table.
As I said, I did come up with a solution but it seems over complicated and I have a feeling that there is a way of doing this with an update. Maybe use the LEN() function but I'm not sure.
Your help would be much appreciated.
BTW, once I've identified the correct Make, I can easily populate my model as all I have to do is use the replace function on VehMakeVehModel column and remove the matched make to get the full model name.
Hope that makes sense and thanks for any help in advance.
View 8 Replies
View Related
Nov 12, 2006
Hi all,
I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value.
I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way...
Here it is:
-- Update a student, by ID.
DROP PROCEDURE p_UpdateStudent
CREATE PROCEDURE p_UpdateStudent
@ID INT,
@NewFName VARCHAR(25),
@NewOName VARCHAR(25),
@NewLName VARCHAR(25),
@NewDOB DATETIME,
@NewENumber VARCHAR(10),
@NewContactAID INT,
@NewContactBID INT
AS
BEGIN
SET NOCOUNT ON;
-- DECLARE THE OLD VALUES
DECLARE @FName AS VARCHAR(25)
DECLARE @OName AS VARCHAR(25)
DECLARE @LName AS VARCHAR(25)
DECLARE @DOB AS DATETIME
DECLARE @ENumber AS VARCHAR(10)
DECLARE @ContactAID AS INT
DECLARE @ContactBID AS INT
-- Get all of the old values
SELECT @FName = FName FROM TBL_Student WHERE ID = 10000
SELECT @OName = OName FROM TBL_Student WHERE ID = 10000
SELECT @LName = LName FROM TBL_Student WHERE ID = 10000
SELECT @DOB = DOB FROM TBL_Student WHERE ID = 10000
SELECT @ENumber = ENumber FROM TBL_Student WHERE ID = 10000
SELECT @ContactAID = ContactAID FROM TBL_Student WHERE ID = 10000
SELECT @ContactBID = ContactBID FROM TBL_Student WHERE ID = 10000
-- USE ISNULL to set all of the new parameters to the provided values only if they are not null
-- Keep the old ones otherwise.
SET @NewFName = ISNULL(@NewFName, @FName)
SET @NewOName = ISNULL(@NewOName, @OName)
SET @NewLName = ISNULL(@NewLName, @LName)
SET @NewDOB = ISNULL(@NewDOB, @DOB)
SET @NewENumber = ISNULL(@NewENumber, @ENumber)
SET @NewContactAID = ISNULL(@NewContactAID, @ContactAID)
SET @NewContactBID = ISNULL(@NewContactBID, @ContactBID)
-- Do the update
UPDATE TBL_Student
SET FName = @NewFName,
OName = @NewOName,
LName = @NewLName,
DOB = @NewDOB,
ENumber = @NewENumber,
ContactAID = @NewContactAID,
ContactBID = @NewContactBID
WHERE
ID = @ID
END
GO
So yeah it works. But As you can see I wish to keep an old copy of the values to perform checks pre update....
Is there any faster way, or am I on the right track? I need a pro's advice :) (before i write all of my procs!!)
Thanks all.
Chris
View 7 Replies
View Related
Mar 7, 2014
I open query analyser and on one tab I update a record in a transaction and hold it.
begin tran
update customers set territory = 'x' where customer = 'A00001'
--rollback tran
In a second tab I attempt to update all records in the table
update customers set carrier = ''
Clear this fails because of the lock placed during the first script and this is fine.
However, is there a way to get the 2nd script to ignore the locked rows and just update as many as it can? The obvious answer seemed to be the READPAST hint like follows…
update customers with (READPAST) set carrier = ''
…but this is still blocked by the original lock. I’ve tried combining it with all sorts of other table hints but all seem to get blocked.
The following does work, ignoring the lock and not returning the data
Select * from customers with (READPAST) where customer = 'A00001'
I’ve tried combining this with the update like so…
update customers
set carrier = ''
from customers with (READPAST)
where customer = 'A00001'
..but this is blocked too.
I’m so desperate I tried moving the update into a cursor and update one row at a time. Nothing worked. I thought I might be able to do something like this
If (Select count(*)
from customers with (READPAST)
where customer = 'A00001') > 0
--then perform update
..but this returns a value of 1 even though the following returns no rows.
Select * from customers with (READPAST) where customer = 'A00001'
View 9 Replies
View Related
Aug 23, 2007
Dear All -
iam facing a problem for passing a parameter to update paradox table statment in the OLE DB Command
Briefly an OLE DB Command exist between OLE DB Source and OLE DB destination , all i want it to send the data that flow from the OLE DB Source to the update paradox table statment in OLE DB Command ,
i use the parameter annotation which is "?" to refer to the parameter in the update where clause and it sucess when i use the OLE DB Command to connect to SQL Server DB but when i use it with the Paradox Table an error appear saying that there is more than one data source column with the name "?" The data source column names must be unique.
i dont know what that means is it means that "?" it consider "?" as a column or what or is there another way to send parameter to Update statment for the paradox table differ from the SQL Server Update statment which is the "? "
i hope someone help me as soon as possible because i need it in work sOOon
Thanks,
Maylo
View 2 Replies
View Related
Aug 31, 2015
I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an
[Code] ....
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?
The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.
View 9 Replies
View Related
Jan 16, 2004
[[Disclaimer: I am by no means a highly-skilled DBA. I have just enough db skills to get my job done, which involves light sql server and oracle tasks. I appreciate the help, guys.]]
I am trying to replace the path section of filenames listed in a collumn of my database. The filenames are UNC paths, and all files have 8 digit filenames with the same extension (they are all tif images). The length of the paths in the filenames will change, however.
Here is what my old paths look like:
\imageimagesNYY-MMABCDEFGH.TIF
..and here is the new path (all of the images are being consolidated into one directory):
\als-imagealscom31imagesdbABCDEFGH.TIF
-------------------------------------------------------------
I have tried using the following query to do this:
update image set filename=replace(filename,'\imageimages5 0-08','\als-imagealscom31imagesdb')
This returns insanely crazy results like 460239 row affected, when it should be about a thousand.
--------------------------------------------
I have also tried the following:
update image set filename=right(filename,12) + '\als-imagealscom31imagesdb' where left(filename,21) = '\imageimages5 0-08'
This affects zero rows.
Please guide me down the one true path. My coworkers are clueless and I'm quickly running out of disk on the old box.
peace,
-jake
View 14 Replies
View Related
Aug 16, 2007
Hi All,
I need to select some collumn data , can i store it in a varable some how.
Select name from Names
how can i assign this value to a variable, I need to manipulate this column value based on few cases.
As always thanks and appreciate your help.
View 1 Replies
View Related
Jan 5, 2007
i have a table like this:
Code:
Product
id - varchar(10)
description - text
the ids are something like this
mod01-01
mod02-01
mod03-01
mod01-02
mod02-02
mod03-02
mod03-03
mod03-04
mod04-01
....
now, i am trying to group by and count all of the ids that end with '01',
i was thinking of something like
select count(id)
group by id
where id= ???
and the second query i need to make is one that counts not one but all the ids that end with the same last 2 digits but in one single query, how can i do that? as a possible hint, is that the only thing i KNOW that the last 2 digits can only be 01,02,03,04 and 05 but still im a bit stuck in here the result should look something like this
id total
01 4
02 3
03 1
04 1
or what suggestions can you give me for this query? even if you just help me with the first query, that would be great, thanks!!
thanks!!!
View 6 Replies
View Related
Apr 19, 2006
This is not a piece of cake as I thought.
Have to replace few characters with few other characters in the string.
Now, I am using nested Replace:
(Replace(Replace(MyString,'Ü','UE'),'Ö','OE')
This example is simplified, number of replacements is more than eight of them.
Is there any other, more elegant, way to do this in just one command?
View 2 Replies
View Related
Apr 19, 2006
This is not a piece of cake as I thought.
Have to replace few characters with few other characters in the string.
Now, I am using nested Replace:
(Replace(Replace(MyString,'Ü','UE'),'Ö','OE')
This example is simplified, number of replacements is more than eight of them.
Is there any other, more elegant, way to do this in just one command?
View 2 Replies
View Related
May 3, 2004
Hi All,
I have a varchar that contains a comma-delimited list of integers, such as "12,34,56,78,123,1,123455".
I need a way to count the number of numbers in the string (or, perhaps, better stated as "I need a count of substrings") ;)
I'm thinking there must be a number of ways to calculate the number of numbers in my list of numbers, but I can only seem to come up with looping through the string/varchar and counting the number of commas, and then adding one to that final count.
Anybody know of a "cooler" way to do this? Everything I can think of involves stepping through a character at a time...
any (printable) thoughts?
THanks!
View 14 Replies
View Related
Apr 11, 2008
I have filename with the following format
461#Pipeline#0978541235#1.pdf
where # is the seperator between different data in the filename, in .NET i achieved to save the different data in the filename to seperate variables like this
Dim siebelId, accountNumber, docType As String
Dim counter As Integer = 0
Dim sites As String() = Nothing
sites = extension2.Split("#")
Dim s As String
For Each s In sites
If counter = 0 Then
siebelId = s
End If
If counter = 1 Then
accountNumber = s
End If
If counter = 2 Then
docType = s
End If
counter = counter + 1
Next s
How can i achieve this in SSIS where i can save these to variables for later use in an Execute SQL Task ?
View 9 Replies
View Related
Jun 19, 2015
I have 2 tables in a SQL Server database:
Table A(Value), which contains some strings in the column "Value"
Table B(Key,Text), which also contains strings (in the column "Text")
Now I want to find all rows in B which contain at least one string of A and create a result table X with all found rows in B. B should contain the found keys and all found substrings for this key (separated with a comma)
The solution I am looking for may not use a Cursor and may not use the CONTAIN-Statement (fulltextsearch feature)..
View 5 Replies
View Related
May 10, 2007
Sorry to ask a stupid question. I have SQL Server 2000 on SBS 2003. I can't find Perform.exe. Anybody know where it should be (I'm sure that you all do!),
Thanks
Colin
View 4 Replies
View Related
Apr 14, 2008
Hi,any body pls help me with the concept called transaction ?Where excatly we are doin this ? In the SQL or through c# codings.i have 3 stored procedures, where i am calling this one by one to perform a single operation in the client side.if any one fails, due to some exceptions. it should rollback and come to the initial state.... How to perform this ? any ideas..
View 8 Replies
View Related
Jan 18, 2003
I have a sp that creates a #temp table and performs a bulk insert.
CREATE TABLE #template (template varchar(8000))
EXEC ('bulk INSERT #template FROM "' + @filename + '"')
SET @html = (SELECT template FROM #template)
DROP TABLE #template
...
When I access this sp with any other user than 'sa' I get this error message:
The current user is not the database or object owner of table '#template'. Cannot perform SET operation.
I've been everywhere (with no success) in the sql srv admin to look for the appropriate check box to allow another named user to access this sp.
Can anyone help?
View 2 Replies
View Related
Apr 14, 2008
IDENTITY_INSERT is already ON for table 'Elbalazo.dbo.DeliveryOption'. Cannot perform SET operation for table 'City'
Here's my entire script:
--------------------------------------------------------------------
/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'
/*Enable Constraints & Triggers again*/
--exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'
-- City
SET IDENTITY_INSERT City ON
INSERT INTO Elbalazo.dbo.City (
[CityID]
,[CityName]
,[CountyID]
,[Active])
SELECT [CityID],[CityName],[CountyID],1
FROM [ElbalazoProduction].dbo.tbl_City
SET IDENTITY_INSERT City OFF
-- State
SET IDENTITY_INSERT [State] ON
INSERT INTO Elbalazo.dbo.State (
[StateID]
,[State]
,[Active])
SELECT [StateID],[State],1
FROM [ElbalazoProduction].dbo.tbl_State
SET IDENTITY_INSERT [State] OFF
-- NumberOfPeopleOption
SET IDENTITY_INSERT NumberOfPeopleOption ON
INSERT INTO [Elbalazo].[dbo].[NumberOfPeopleOption]
([NumberOfPeopleOptionID]
,[NumberOfPeopleNameOption]
,[Active])
SELECT [NumberOfPeopleID], [NumberOfPeopleName],1
FROM [ElbalazoProduction].dbo.tbl_NumberOfPeople
SET IDENTITY_INSERT NumberOfPeopleOption OFF
-- DeliveryOption
SET IDENTITY_INSERT DeliveryOption ON
INSERT INTO [Elbalazo].[dbo].[DeliveryOption]
([DeliveryOptionID]
,[DeliveryOptionName]
,[Active])
SELECT [DeliveryOptionID], [DeliveryOptionName],1
FROM [ElbalazoProduction].dbo.tbl_DeliveryOption
SET IDENTITY_INSERT DeliveryOption OFF
-- User
SET IDENTITY_INSERT [User] ON
INSERT INTO [Elbalazo].[dbo].[User]
([UserID]
,[FirstName]
,[LastName]
,[Address1]
,[Address2]
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[Email]
,[CreateDate]
,[Active])
SELECT [CustomerID]
,[FirstName]
,[LastName]
,[AddressLine1]
,NULL
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[EmailPrefix] + '@' + [EmailSuffix]
,[CreateDate]
,1
FROM [ElbalazoProduction].dbo.tbl_Customer
SET IDENTITY_INSERT [User] OFF
-- EntreeOption
SET IDENTITY_INSERT EntreeOption ON
INSERT INTO [Elbalazo].[dbo].[EntreeOption]
([EntreeOptionID]
,[EntreeOptionName]
,[Active])
SELECT [EntreeOptionID]
,[EntreeOptionName]
,1
FROM [ElbalazoProduction].dbo.tbl_EntreeOption
SET IDENTITY_INSERT EntreeOption OFF
-- CateringOrder
SET IDENTITY_INSERT CateringOrder ON
INSERT INTO [Elbalazo].[dbo].[CateringOrder]
([CateringOrderID]
,[UserID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID])
SELECT [CateringOrderID]
,[CustomerID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder
SET IDENTITY_INSERT CateringOrder OFF
-- CateringOrder_EntreeItem
SET IDENTITY_INSERT CateringOrderEntreeItem ON
INSERT INTO [Elbalazo].[dbo].[CateringOrderEntreeItem]
([CateringOrderEntreeItemID]
,[CateringOrderID]
,[EntreeItemID])
SELECT [CateringORder_EntreeItemID]
,[CateringOrderID]
,[EntreeItemID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder_EntreeItem
SET IDENTITY_INSERT CateringOrderEntreeItem OFF
select * from BeanOption
select * from CateringItemIncluded
select * from CateringOrder
select * from CateringOrderEntreeItem
select * from CateringOrderEntrees
select * from City
select * from Country
select * from DeliveryOption
select * from EntreeOption
select * from NumberOfPeopleOption
select * from [State]
select * from [User]
View 15 Replies
View Related
Aug 10, 2006
Hello all.
I am looking to perform a calculation and enter the reult into a field within my table. The fields that I need to base the calculation on are all in one table (SALARY). The fields are: SALARY and BASIC_HOURS and the result is to be entered into field HOURLY_RATE. The actualy calculation to be preformed is:
hourly rate = ()salary / 52)/basic hours)
Thanks for reading :)
View 4 Replies
View Related
Aug 31, 2007
Hello,
i am using Full text search for my web application search SP.
i have used Contains for Exact search and Freetext for Any word kind of search.
now i want to perform Like search using Contains.
same behaviour which we can get using LIKE word. (e.g. Select * from tbl where field like 'abc%')
even i have tried using contains(title,'"*Documen*"') . but its not working.
Kindly Let me know how to perform this.
Thanks.
View 3 Replies
View Related
Oct 11, 2006
I am using this SQL in a stored procedure:
INSERT INTO staff_address (staff_number, block_number, block_name) VALUES(@staff_number, @block_number, @block_name)
and I want to update the value @block_number from blocks database with:
SELECT * FROM blocks WHERE block_number = given_block_number
View 5 Replies
View Related
Sep 29, 2006
What i want:
Receive files from remote server every 8 hours daily,and if these files are all created during this period ,then execute next step
My thought:
I can receive files using FTP Task and i can set up job from Sql Agent
but how can i say whether these files are new or old?
Any idea? Thanks
View 4 Replies
View Related
Sep 5, 2006
this is my code: 1 myconnection = new SqlConnection(kiemtraloi,chuoiketnoi);
2 mycommand.Parameters.Add(new SqlParameter("@mancc",SqlDbType.VarChar,10));
3 mycommand.Parameters["@mancc"].Value = TextBox1.Text;
4 myconnection.Open();
5 myreader = mycommand.ExecuteReader();
6 if(myreader.HasRows)
And when I click to start button, a error appear: NO overload for method 'sqlconnection' takes '2' argumentsI don't know what i must do :(, please help methx
View 4 Replies
View Related
Dec 11, 2007
My e-commerce site is currently running the following process when items are shipped from the manufacturer:1. Manufacturer sends a 2-column CSV to the retailer containing an Order Number and its Shipping Tracking Number.2. Through the web admin panel I've built, a retail staff member uploads the CSV to the server.3. In code, the CSV is parsed. The tracking number is saved to the database attached to the Order Number.4. After a tracking # is saved, each item in that order has its status updated to Shipped.5. The customer is sent an email using ASPEmail from Persits which contains their tracking #.The process seems to work without a problem so long as the CSV contains roughly 50 tracking #'s or so. The retailer has gotten insanely busy and wants to upload 3 or 4 thousand tracking #'s in a single CSV, but the process times out, even with large server timeout values being set in code. Is there a way to streamline the process to make this work more efficiently? I can provide the code if that helps.
View 5 Replies
View Related
May 5, 2008
Hi all,
Lets say that i have 3 tables, they are already related together, information on each of the 3 tables and their relationship between eachother can be found below:I am using Microsoft SQL Server 2005 Database in Visual Web Developer 2008 using VB programming (ADO.NET)The 3 tables are as follows:
FloorRoomLocation
Fields inside each of the tables can be found below:
Floor Table(PK) ID(Required) NameDisplayOrder
Room Table(PK) ID(Required) Name(PK, FK) FloorIDExchangeIDExtensionIPDeviceNameRemarks(Required) Equipment(Required) Floor2IDFloorLocationXFloorLocationY
Location Table(PK,FK) FloorID (Required) CountryName(Required) CityName(Required) BuildingName
The Relationships between the 3 tables are as follows:
ID field in Floor table is referenced by the field named FloorID in the Room Table
ID field in Floor table is referenced by the field named FloorID in the Location table
NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table.
Floor Table has a 1 to Many Relationship with the Room Table.
I Have already done the physical relationships between each of the 3 tables in the Database Diagram, in Visual Web Developer 2008 Express Edition under the Database Explorer
So i have created this form to accept user input:
this form is in a aspx page called AddRooms.aspx
Country Name:______________ (data sent to location table)
City Name:_________________ (data sent to location table)
Building Name:______________ (data sent to location table)
FloorID:[This is a DropDownList which is databinded to the Floor Table]
Room Name:_______________ (data sent to room table)
*Just imagine that the above is a form to accept user input
*With a hit of a submit button all data will be sent to their respective tables in the database
Now i know that i have to JOIN tables in my database in order for my application to work, but i have no experience performing JOIN, i need great guys like you to help me apply JOIN to the 3 tables mentioned above, do let me know if you have any doubts in what i am trying to do.
Do give me that code that i can either apply in the (VB environment Visual Web Developer 2008) and/or in Microsoft SQL Server 2005 environment
Just need the SQL Statment that i will need to construct to perform the JOIN i need to.
Please Help
Many Thanks : )
View 2 Replies
View Related
Feb 15, 2005
I want to build some paging functionality into my repeater (b4 you ask, datagrid not providing flexibility required for presentation).
I will have no problem with the VB logic but I will need to execute SQL that only returns results from x to y (e.g. results 21 thru 40 for page 2). I know I can do something like 'SELECT TOP 20 * FROM...', or something like it, for page 1. But, I'm not sure if it's possible to build SQL for the pages greater than 1. Any suggestions.
Thanks
Martin
View 1 Replies
View Related
Mar 22, 2007
I had my IT dept. install sql server 2005 enterprise edition on a new windows server 2003. All other machines are running sql 2000 server on windows server 2000. I am a sa and local admin on all servers. I tried performing copy database, backup and restore and detach and attach to upgrade the sql server 2000 databases to 2005 and all fail. I followed all steps. I was imformed that IT installed sp2 for sql server 2005. It's like the servers dont talk to each other. Their all on the company's domain. I am wondering if something happen with the install but the IT dept. insist that everything went fine. It's strange that I can't perform a simple backup and restore it on the new server buy when I click on restore it doesn't let me browse to get the backup file on the 2000 server. I never had a problem in Sql 2000 with backup. Can it be the installation was corrupted somehow. It seems fine. I haven't created any new databases because I wanted to move the databases from sql 2000. Can anyone help me get a clue of what the problem is please?
Thanks
View 2 Replies
View Related
May 31, 2008
Hi,
Appreciated someone able to provide me some clu on how to perfom DBCC CHECKDB. Is it necessary to set the database to "Read only" before execute DBCC CHECKDB?
Appreaciated your help.
Regards,
Jimmy Liew
View 3 Replies
View Related
Mar 22, 2007
I had my IT dept. install sql server 2005 enterprise edition on a new windows server 2003. All other machines are running sql 2000 server on windows server 2000. I am a sa and local admin on all servers. I tried performing copy database, backup and restore and detach and attach to upgrade the sql server 2000 databases to 2005 and all fail. I followed all steps. I was imformed that IT installed sp2 for sql server 2005. It's like the servers dont talk to each other. Their all on the company's domain. I am wondering if something happen with the install but the IT dept. insist that everything went fine. It's strange that I can't perform a simple backup and restore it on the new server buy when I click on restore it doesn't let me browse to get the backup file on the 2000 server. I never had a problem in Sql 2000 with backup. Can it be the installation was corrupted somehow. It seems fine. I haven't created any new databases because I wanted to move the databases from sql 2000. Can anyone help me get a clue of what the problem is please?
Thanks
View 1 Replies
View Related
Jul 23, 2005
hey guys--i need some expert advice!we have a Dell PE 2650 with 2GHz and 2MB RAM with 2000 Server. on it wehave about 6 MSSQL DB's. a couple of the DB's are the back-end for adecent sized web app (5-10 users on average, 20 users at most). manytimes, the cpu skyrockets on our db server when using the web app.sometimes things timeout, other times not. at this time, upgrading thehardware is not an option. aside from increasing the timeout periods, iwas wondering if windows 2003 server would utilize the processor better(even just a little bit?) enough to know the difference.should i attempt the upgrade, or is 2003 no more efficient withprocessor resources?thanks a bunch!
View 4 Replies
View Related
Sep 14, 2007
I'm working with ADO 2.8 en C++ with Visual Studio 2005. I want to perform a "select" in asynchronous mode. I don't really understand the logical of the recordset events. For example, I received a number of MoveComplete event higher than the number of rows in my recordset. It is really not clear for me ...
Does someone knows where I can find a a good example of C++ (or VB) code to manage select statements in asynchronous mode ?
Thanks in advance for your help.
François.
View 1 Replies
View Related
Nov 14, 2007
Hi everyone!
I am building a database in VC++2005 Express, connecting to SQL Server. I planed to add Delete function to a button, and when clicked, the data with the specific ID according to the IDTextBox->Text would be deleted from the table. However, nothing happened. I don't know what's wrong with my program.
I have attached the button_Click here. Thank you!
private: System:: Void button8_Click(System:: Object^ sender, System:: EventArgs
^ e) {
System:: Windows:: Forms:: DialogResult^ MyDlg=MessageBox:: Show("Are you sure
to delete?","Warning",MessageBoxButtons:: YesNo,MessageBoxIcon:: Question);
if (MyDlg==System:: Windows:: Forms:: DialogResult:: Yes)
{
if (this->sqlConnection1->State==ConnectionState:: Open)
{
this->sqlConnection1->Close();
this->sqlConnection1->ConnectionString="Data Source=(local);Integrated S
ecurity=SSPI;database=Pnemoconises";
this->sqlConnection1->Open();
SqlTransaction^ MyTransaction=this->sqlConnection1->BeginTransaction();
try
{
this->sqlCommand1=this->sqlConnection1->CreateCommand();
this->sqlCommand1->CommandType=CommandType:: Text;
this->sqlCommand1->CommandText="DELETE FROM [Basic_Info] WHERE Patient I
D='"+Convert:: ToString(this->Patient_IDTextBox->Text)+"'";
this->sqlCommand1->Transaction=MyTransaction;
this->sqlCommand1->ExecuteNonQuery();
MyTransaction->Commit();
}
catch (SqlException^ ex)
{
MyTransaction->Rollback();
}
catch (Exception^ ex)
{
MessageBox:: Show(ex->Message,"Information",MessageBoxButtons:: OK,Message
BoxIcon:: Information);
}
finally
{
this->sqlConnection1->Close();
}
}
}
}
View 4 Replies
View Related
Nov 6, 2006
hi
I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .
Thanks
Mani
View 9 Replies
View Related
