Need Emergency Help With SQL Query (difficulty: Moderate)
Nov 30, 2006
Panicked, as usual. Who says it's great being a sole proprietor?
Using MS-SQLServer:
Three tables: A_Appointment, AX_Appointment_Entity and E_Entity.
AX_Appointment_Entity is an intersect/association table between
A_Appointment and E_Entity. One appointment may have many attendees
(Appointment_Entity). One attendee (Entity) may have many
appointments.
My task is to delete all appointments and related
AX_Appointment_Entity rows where the number of attendees is one or
less.
I'm great at standard select or delete queries. Because this straddles
tables in a way I'm less skilled in, I'm pleading for help.
Thanks for any ideas. fwiw, I'm using Transact-SQL.
BLink
--------------------------
"The worst thing about censorship is [redacted]"
Hi all - had a bit of a nightmare at work with order accounts application. Basically - all the orders have been set to uninvoiced and I need to set the ones which have been invoiced back to invoiced. Problem is that orders can be part invoiced.
Tables as follows:
orders orderid PK int invoiced int
orderline orderid int qty int productid int
invoices invoiceid PK int orderid int
invoiceline invoiceid int qty int productid int
There can be multiple invoices for each order
Question is - how can I write a query which updates the order table - set invoiced to 1 where there are the same amount of items in the order as in the related invoices?
In SQL Server 20:Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:
CREATE PROCEDURE [SP_UPDATE_T_Site](@old_I_SiteID bigint,@new_V_SiteName varchar(50),@new_V_Address1 varchar(50),@new_V_Address2 varchar(50),@new_V_Address3 varchar(50),@new_V_TownCity varchar(50),@new_I_RegionID bigint,@new_V_Postcode varchar(10))ASUPDATE T_Sites SET[V_SiteName] = @new_V_SiteName,[V_Address1] = @new_V_Address1,[V_Address2] = @new_V_Address2,[V_Address3] = @new_V_Address3,[V_TownCity] = @new_V_TownCity,[I_RegionID] =@new_I_RegionID ,[V_PostCode] = @new_V_PostcodeWHERE I_SiteID = @old_I_SiteIDGONow, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:EXEC SP_UPDATE_T_Site @I_SiteID='2', @Address1="....."I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?
I have created a a web application on computer1 and I have now transferred it to computer2. I am trying to attach my database (db1) in VS but I get this error: "Cannot open user default database. Login failed for user: username1". However when I use Management Studio Express I am able to access db1. I'm a newbie on this.
I'd like to select all metal types, and the weight of any metal that has a matching record for a given object ID. So for ObjectID 1, I want a result set like this:
I've tried a left outer join on the assigned metals table, but only got the one matching record back. Also if possible, the ideal result would also indicate if it had a match:
Hey guys, I am a newbie here, so please bear with me. I have installed SQL Server 2005 CTP on Machine A and MSDE on Machine B. Both these contain the PUBS database beside others. On Machine C, I have installed "ASP.Net Web Matrix" and I want to be able to connect to either Machine A or B to access the PUBS database. It connects to Machine B, when I put in the Servers IP address. But everytime I try to connect to Machine A, it gives me an error saying, "Unable to connect to the database. To connect to this server you must use SQL Server Management Studio or SQL Server Management objects(SMO) ". I heard that connection strings are needed to access the SQL Server 2005 but I have no idea where to begin. Do I need to create special users to access the Server or will Windows authentication work ? Could anyone help me out in connecting to the SQL Server 2005 CTP?
I am trying to transfer data into SQL Server from Excel using DTS.
I get error for a particular field:
Data for Source Column 3('Col3') is too large for the specified buffer size.
I gathered from other resources, that this error is due to some record in the excel sheet for which that particular field is too big. and it reads only the first 8 records to determine a data type.
the fix is to chnage the registry value, as mentioned in the above link. But hat is not an option for me, as that slows down the process a lot.
Does any body else has any other suggestion for this?
I have an idea, that worked, but for that I have to manually insert a record on the top in the excel sheet, with a big size data in that field (which gives problem), and then run DTS, later delete that record.
To automate this idea, I tried to do a DTS from a dummy table (with a record with big data in it) to this excel sheet, but it puts the record at the bottom of the sheet. Is there any way to put this record on the top?
Any help in this regard will be greatly appreciated.
I installed SqlExpress with Advanced services on to a server running Small Business server 2003.
I needed to uninstall SqlExp, and thought I did so through Add/Remove programs. For some reason it did not seem to uninstall. Foolishly I did not check, and then tried to install another copy in a different directory. I've then ended up with complete mess.
Tried to do another uninstall, but again, ended up with two incomplete versions of express.
Is it possible that part of my problem revolves around the fact I have a mirrored drive with Raid 1 installed.??
Whatever, I need to know how to clean up the mess and start again. Preferably without having to do a format and reinstall SBS.
We are using a system that has uses four instances. On each client to the server we used the ODBC settings to connect to the different instances. We have since reinstalled the server, and now we are unable to find or 'see' any of the databases, except for locally.
E.g. The first database we install is named Foo, with database Bar. It is set up on port 1433. The second named instance is Foo2, with database Bar2. It is set up on a dynamic port.
We now try to connect to the database with ODBC. We connect to SERVERFOO, and then select the BAR db. It works fine. We then try to connect to the database SERVERFoo2, it lets us in, and then all we can select is the BAR db. I don't know what is causing this. We never before had to edit the settings in the client config for the odbc settings (e.g. to set manually to TCP port 1433) and I have checked the sql config manager to ensure that tcpip settings are allowed and will "ListenAll".
What more do we need to do? I think the browser should be installed, but my collegue disagrees.
Any ideas as to why we can only see the database referenced to 1433 port, and why no other port works, when we never had to do that before?
Hello All,I am having trouble with a stored procedure I am working on. Just a little background, I am trying to do a GridView inside another GridView but the trouble I am having is that each row in the Parent GridView is display the entire Child GridView. What I want to do is basically for GridView1 (Parent) Row 1 should display GridView2 (Child) Row1. So I thought let me just pass three parameters.Any Help would be greatly apperciated Field – The Column I wantProject – The Project IDTPC – Name This is the stored procedure I am working on… CREATE PROCEDURE dbo.Portal_FinancialGetJanuary( @TPC nvarchar(50), @Project int @Field varchar(1000)) AS SET NOCOUNT ON SELECT @Field, MonthFROM Portal_FinancialForecastingWHERE(((Portal_FinancialForecasting.TPC) = @TPC) AND (Portal_FinancialForecasting.Month = 'January') AND (Portal_FinancialForecasting.Project = @Project))
hi, i am getting problem in sql65, service pack5a. for some tables in my database it is showing null. I cannot able to see the records in a table. I can see these tables in sysobjects table.
owner of the some tables is "null". I executed following query to get the table name as well as owner.
select owner = user_name(uid), name , type from sysobjects where name = 'published_docs' Result:
owner name type ----- ----- ----- (null) published_docs u
--Pls give any ideas. Dont say go to 65 discussion board(sorry i am in urgent) thank you very much
Hi all, My window xp pro suddenly cannot reboot any more. luckily, I can get into safe mode with command prompt. So before doing any repair or even reinstallation of the op. I need to back up my database using transact-sql in order to restore later on completely if have to reinstall my OP system. I have MSDE installed on my computer. so osql.exe is available for me to back up the database. I did before with the backup command, but it's a while ago. since this time the backup is so precious to me. I want you guys to back me up. I will go to osql dir in C:program filesmicrosoft SQL server... then issue: backup database CMCTIS to disk='D:sqlbackupcmcbackup.dat'
CMCTIS is the database name and d:sqlbackupcmcbackup.dat is the file I want to use later on to restore. Since if I will probably do repair or reinstallation, the information on C: will be wiped out or damaged. that's why I will use D: drive for the backup. Please confirm.
So later on I can restore database completely including data and store procedures, database changes, right? I have several databases and two server instances. I have to back up database one by one right?
Please reply at your earliest convenience. Otherwise I will lose all three month database work.
our one of our website is down. I try to reboot the server and restart the services. It didn't help. One of our user try to ftp. But also it said he has no permission on it. Do you know what can i do?
I have just deleted a clients database by mistake ( working too many hours this weekend!). I have recovered the actual files with Ontrack file recovery software.
I have tried to attach the mdf but SQL says it's not a valid sql database file. I have also created a new database of the same name , stopped sql, copied over the original files and re-started sql . SQL ( 2000) then marks the datbase as suspect.
Does anyone have a possible
thx
Stressed & Desperate.
No backups available . No lectures please... i've just learn't the VERY hard way !!
I don't know why the last topic is locked, but here is the situation more detailed.
I know that the group by is the answer for my last question.
But the situation is sadly not this easy:
We have a Donortype table and it looks like this:
D DONORTARGETQ1 DONORTARGETQ2 DONORTARGETQ3 DONORTARGETQ4 - ------------- ------------- ------------- ------------- C 400 400 500 500 F 300 350 400 500 I 125 150 200 275
3 rows selected.
The first column is called donortype. That's what we have to use.
The contribution table looks like this:
CONTRIBUT DONORID CONTRIBUTIONAMOUNT PROGRAMNAME MEMBERNUMB --------- ---------- ------------------ ------------------------------ ---------- 03-JAN-06 2 200 Basic Research 5 07-JAN-06 3 150 Applied Research 3 27-JAN-06 7 50 Teaching Programs 1 05-FEB-06 6 100 International Programs 2 23-FEB-06 2 50 International Programs 1 03-MAR-06 1 100 Basic Research 6 30-MAR-06 5 75 Basic Research 1 15-APR-06 6 50 Teaching Programs 4 09-MAY-06 4 100 Applied Research 2 14-MAY-06 7 100 International Programs 5 29-MAY-06 7 100 Applied Research 5 11-JUN-06 5 50 Teaching Programs 5 18-JUN-06 6 75 Applied Research 2 23-JUN-06 3 50 Basic Research 3 10-JUL-06 1 100 Teaching Programs 3 16-JUL-06 4 100 International Programs 2 22-JUL-06 6 75 Applied Research 4 15-AUG-06 5 60 Applied Research 1 23-AUG-06 2 150 International Programs 1 09-SEP-06 3 175 Applied Research 6 18-SEP-06 6 150 Teaching Programs 2 01-OCT-06 2 80 International Programs 5 13-OCT-06 1 50 Basic Research 6 30-OCT-06 5 25 Applied Research 1 11-NOV-06 4 100 Teaching Programs 4 24-NOV-06 2 100 Applied Research 5 10-DEC-06 7 200 Basic Research 1 20-DEC-06 3 85 International Programs 6
28 rows selected.
We have the DonorID and the ContributionAmount here.
We also have a Donor table:
DONORID DONORNAME D DONORPROJECTION ---------- ------------------------------ - --------------- 1 Crabtree I 50 2 Genetix C 100 3 Marlo I 55 4 Mervis F 60 5 Orange Computers C 50 6 Roper Foundation F 70 7 Keltron, Inc. C 85
7 rows selected.
The third column is DonorType as well.
What I have to create is something like this
DonorType ContributionAmount I 100 F 300 C 250
Where the 100 is the sum of ContributionAmounts performed by DonorID-s which have a DonorType I
and 300 is the sum of ContributionAmounts performed by DonorID-s which have a DonorType F
and so on...
(the numbers are made up, they are probably not correct...)
My idea was that let's create a view with DonorID and sum(ContributionAmount) grouped by DonorID.
Than join this view with the DonorType table, and create a view which contains DonorId, DonorType and sum(ContributionAmount) from the first view.
And what now?! HOw do you group this by DonorType?
can anyone tell how to connect to Microsoft Mobile Server 2005 using System.Data.SqlceConnection or using System.oledbConnection connect to Microsoft Access DataBase in Pocket Pc or windows CE program?
dose there any minimum requirements i need to install?would you like to list a list for me
Hello frdz, I have two tables in sqlserver 2005. I have created the stored procedure for insert,update data.I m creating my application in asp.net with C# Table-1 CUSTOMERFields:customerid int identity,cardid int,customername varchar(20) not null,address varchar(20) not null,city varchar(20) not null,emailid varchar(20) Table-2 CARDFields:cardid int identity,cardtype varchar(20) not null,carddetails varchar(20) not null INSERT INTO CUSTOMER (customername ,address,city,emailid) VALUES (@customername,@address,@city,@emailid) SELECT @customerid = SCOPE_IDENTITY()/* HELP HERE NOT ABLE TO GET DATA OF CARD */ SELECT @cardid = cardid from CARD where customerid =@cardid Pls tell me how to insert the data ...There is only one cardid for only one customerid both should be unique no duplication....One customer can have only one and one card...
I am trying to create stored procedure i Query analyzer in visual studio 2005. I am havingdifficulty though. Whenever I press the execute button, here is the error message I get: Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21Incorrect syntax near 'MarketName'. Here is the stored procedure. Note that the very first column in named "MarketId" but I did notinclude it in the stored procedure since it should be auto generated. USE [StockWatch]GO/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[MarketCreate] ( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2), @MarketDescription nvarchar(100)) ASINSERT INTO Market( MarketCode MarketName LastUpdateDate MarketDescription)VALUES( @MarketCode @MarketName @LastUpdateUser @MarketDescription)
I created a web form where the user fills in some data and when he submits the form, I do an insert into he database table. The problem is, how can I get the data from the form into the insert statement?. here is the code: Dim Message As String Dim connStr As String Dim myConnection As SqlConnection Dim mySqlCommand As SqlCommand connStr = "server=SIMIVSdotNET;Trusted_Connection=yes;database=AeroSea" myConnection = New SqlConnection(connStr) mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,name.text)", myConnection)
If I execute the above code, then nothing gets updated. When I change the insert staement into the following, mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'myname')", myConnection) then, value 1 for customerID field and myname in the namefield is added. I know I am doing something stupid, but can't figure it out. I am confused. please help me.
This is probably a very simple question but i am having problems with inserting information into database The function takes the values "FirstName" And "LastName" from A table Called "Customer" and the value "ProductID" from a table called "Products" and inserts them into a table called " NewOrder". Everything compiles ok but when I press the button the information is not uploaded to thedatabase. ( There is no error message) This is the stored procedure CREATE PROCEDURE SP_NewOrder(@CartID char (36), @CustomerID Varchar (50))AS INSERT INTO NewOrder (FirstName, LastName, ProductID) SELECT Customer.FirstName, Customer.LastName, Products.ProductID From Customer,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE ShoppingCart.CartID = @CartID AND Customer.CustomerID = @CustomerIDGO This is the Function Public Shared Function CreateOrder() AS String Dim customerID As integer Dim connection as New SqlConnection(connectionString) Dim command as New SqlCommand("SP_NewOrder",connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@CartID", SqlDbType.Char,36) command.Parameters("@CartID").Value = shoppingCartID command.Parameters.Add("@CustomerID", SqlDbType.Varchar,36) command.Parameters("@CustomerID").Value = customerID Try connection.Open() command.ExecuteNonQuery() Finally connection.Close() End TryEnd Function This is the page code Sub btn3_Click(sender As Object, e As EventArgs)Dim cart as New ShoppingCart()Dim shoppingCartID As IntegerDim customerID As Integer = Context.Session("worldshop_CustomerID")cart.CreateOrder()End Sub Can Anyone see where I am going wrong Many thanks martin
I'm new to SQL Server and I'm using 2005 Express with SSMSE, and having trouble adding a record to a table "manually" by typing in the data.
I find I can change most of the fields by viewing the table in SSMSE and just clicking on the field and editing it then clicking "execute SQL" but for some reason the changes don't "take" when a date field is involved.
Is there a special or proper way to enter a date that I'm not aware of? I'm not having much success figuring out this online help stuff from within SSMSE, I keep getting to help on the help system itself (oops).
I am probably missing something simple here but...
I have a CLR Stored Procedure where I am attempting to execute multiple SQL commands. Here is what happens.
Case One 1. Stored Procedure is invoked. 2. Create SQL Connection. 3. Open the connection. 4. Begin Transaction. 5. Create Command from Connection. 6. Set Command objects Transaction property to transaction created above. 7. Set Command Text Property to SQL Query. 8. Execute Query via ExecuteReader and get SQLReader. 9. Read query result set. 10. Close QueryReader. 11. Set Command Text Property to another SQL Query. 12. Execute Query
This is where I get one of two exceptions. 1. An Exception stating that I cannot have parallel transactions. or 2. An Exception that I have an open SqlReader that must be closed.
Case Two 1. Stored Procedure is invoked. 2. Create SQL Connection. 3. Open the connection. 4. Begin Transaction. 5. Create Command from Connection. 6. Set Command objects Transaction property to transaction created above. 7. Set Command Text Property to SQL INSERT command. 8. Execute Insert command. 9. Set Command Text Property to SQL Query command. 10. Execute Query Command via ExecuteReader.
At this point the stored procedure appears to hang.
Could someone please enlighten me if I have missed something obvious while reading the manual, regarding what you can and cannot do?
Dear Experts,I've worked with Oracle since 1995. I have gonevery deep into many of the Oracle features, includingsqlloader, and export/import. And I've done datamodelling even longer.At the same time, I have done ETL since 1995.Although, at the Proc, and PLSQL, sqlloader, level.Map the data. Take data from the source, do anymodifications/transformations that are required,and insert or update. Easy. The hard part isdetermining the mappings.But I have not been given the opportunity towork much with ETL tools such as:Informatica, DataStage, Ascential, Ab InitioThe little bit of experience that I do have,showed that Informatica was -incredibly- easy to use.It's a GUI. It's SUPPOSED to be simple!!!I have missed out on a number of opportunitiesbecause I didn't have a few YEARS of experiencewith ETL tools. Which seems odd, because thetool looks like it requires a max of a week to learn!Just how difficult are these ETL tools to learn?Especially if you have already been working withOracle, and doing data mappings and loads since 1995?I'm thinking that it can't be difficult at all.When you search for newsgroups, there are no newsgroupsfor these tools. Although the companies who makethem, might have their own newsgroup.There are also not many books on these tools.If you search for books on Oracle, or SQL Server,you will find a lot of very big, and detailedbooks. But there is next to nothing specificallyon the various ETL tools.Any certifications for any of these tools lookto be much simpler than Oracle's.To me, the real issue is the mappings. If youcome into a new environment, the data mappingsare completely esoteric to those systems.No amount of experience with an ETL tool, or anytool, is going to tell you what the mappings should be.Questions:- Just how difficult are these ETL tools to learnfor an experience Oracle pro like myself?- Other than a GUI, making everything simple to use,just what are the advantages of using ETL tools?- what built in functionality do ETL toolshave, that can't be done in PLSQL?Thanks a lot!
I have been running SQL Server 2005 Express (basic) Instance MSSMLBIZ in conjunction with Microsoft Office Accounting 2007 Professional.
I want to make a "clean install" of SQL Server 2005 Express Advanced, SQL Server2005 Express Tools, in order to create additional Reports in Microsoft Office Accounting 2007 Professional.
I ran uininstall on the basic SQL Server 2005 Express, but Native Client did not uninstall; the following message was posted:
"An installation package for the product Microsoft SQL Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'."
I then attempted to install SQL Server 2005 Express Advanced, but it failed to load Native Client. I then uninstalled all instances, deleted the SQL Server Express Files in Programs, and ran "CC Cleaner" to clear the Registry.
I then attempted to uninstall Native Client again, and received the following message:
"This action is only valid for products that are currently installed."
I the returned to CCCleaner Tools to attempt to delete the residuals and received the following message
"Cannot delete msi installer."
I ran "Search - *.msi" and there is no instance of sqlncli.msi visible.
I'm developing an application in VB 2005 Express using SQL 2005 Express. I need to put a timestamp into my table each time I create a row...
The following is a snippet...
Dim DDate As [SqlDateTime] = Now()
Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' " & DDate & " ')"
Which won't work as I am attempting to concatinate a SqlDateTime into a string.
My best guess is that I need to somehow to use a DEFAULT value in the table that persists so each time a row is created the datetime it was created is saved with the row, rather than being re-calculated each time the table is opened. There are probably several other ways of doing it and this may not be the easiest.
I'm not a programmer, just an Engineer, so I can only read Help for 5 minutes at a time.
I am trying to replicate between two servers. I am pushing a subscription to the other server. Of course this has to write to a file on the Servers hard drive before the actual replication. (It's actually trying to write a shared drive on the server) I have checked the permissions all day and I keep getting the same thing; 'The process could not create file [path to file] the network name cannot be found' What should I check? I have checked the permissions on the drive all the way down to the folder and It hasnt helped. I have checked the host files to make sure the ip address is correct in the file. I dont understand the network name error because it's trying to write to itself. Is there anyone out there that can help me???? PLEASE????
We have a big concern in our Database system. We have 2000 transactions daily in our database. We need to replicate some how the database for our fail over setup. I tried transactional replication at midnight but our all systems locked and we had a lot of complaints from the customers and It was taking a lot of time to snapshot part and I had to abort it because of these reasons. I need an advice how I can create a replication or is there any other way to replicate or is there any way to do this process without bothering the system? Please give me some advice and help..
I have a 6.5 DB in which I lost a hard drive and have to recreate the Databases. I do have the original and latest device file ***.DAT which contains all of my application Data. Is there a way I can re-create the DB and point to use this device and keep my data???
Here is the code, if somebody is interested about it. It is oracle, not SQL server, but the idea is the same.
drop view a; drop view b; drop view figure5_9;
create view a as select d.donorID, dt.donortype, dt.donortargetq1 from donortype dt, donor d where d.donortype=dt.donortype;
select * from a;
create view b as select a.donortype, sum(c.ContributionAmount) as amount from Contribution c, a where c.ContributionDate between '01-JAN-06' and '31-MAR-06' and a.donorID=c.donorID group by a.donortype;
select * from b;
create view figure5_9 as select b.donortype, a.donortargetq1, b.amount, b.amount/a.donortargetq1 as percentage from a, b where a.donortype=b.donortype group by b.donortype, a.donortargetq1, b.amount;
Dear All,We have a big concern in our Database system. We have 2000 transactionsdaily in our database. We need to replicate some how the database forour fail over setup. I tried transactional replication at midnight butour all systems locked and we had a lot of complaints from thecustomers and It was taking a lot of time to snapshot part and I had toabort it because of these reasons.I need an advice how I can create a replication or is there any otherway to replicate or is there any way to do this process withoutbothering the system? Please give me some advice and help..Thanksasa.
Hi... I am new to MS SQL 2000. I need to generate reports using the SQL2000 database and exporting the data to Excel. Currently i started using the DTS services and was fine..But i have constraints that Excel can hold only 65K records..My records can go up to 80K +.Is there a way i can split the sheet depending the number of records..or is there a better way to do this...I do not have reporting services as we r using SQL 2000...Could anyone please help...
If there is any other way of doing this please guide me as to how to go about or any reference websites...
