NOT FOR REPLICATION Causing PK Errors
Nov 30, 2007
Hi all
I have set up Transactional Replication from 2005 to 2000. After running a few transactions I disabled the replication, pointed one of our IIS servers to the 2000 database to do a quick functional test and validate replication. We are getting "Cannot insert duplicate value in object XXXX with violation of PK constraint errors. I was able to figure out the cause. This was happening for tables with Identity columns which have been set NOT FOR REPLICATION. The IDENT_CURRENT values are different than the MAX value in the table. so doing a DBCC CHECKIDENT with RESEED seemed to have fixed the issue. Now I am running the DBCC command on ALL tables that have been used for Replication and have NFR set to true. Its taking a while as the tables are big.
Now I am wondering if anyone has faced similar issue? Is this a known issue? I have googled and have found nothing or no one complaining about this. I believe most people have used replication for reporting where they are just querying the databases. We are testing replication as a fall back scenario.
Opinions welcome..
thanks,
Don
View 8 Replies
ADVERTISEMENT
Feb 28, 2007
Hi All,
I tried to create a new project in BCM and I get an error indicating that there is already a project with that name. There is no project with that name in my list of projects! However, I think during one of the frequent installs and uninstalls before I was able to install a somewhat stable BCM, i might have created a project with that name prior to one of the many crashes (it still hangs and has to be killed in task manager periodically). I suspect I might have a rogue database that was created during one of the reinstalls which included installing and uninstalling small business accounting, which I have since also uninstalled. I have tried to use studio manager express to look at the tables in the databases, of which there are two, "mssmallbusiness" and "contctmgr 16022007" , in order to find an instance of a record with the name in question, no joy yet. Do I need the "mssmallbusiness" database to run BCM and if not can I delete it? I suspect that the mssmallbusiness database could be interfering somehow with the "contctmgr 16022007".
Since installing BCM, my outlook has slowed down considerably and hangs often on launch, I would abandon BCM, but I think it's a really cool tool and I would like to install small business accounting too! My machine is a centrino duo with 1 GB RAM and plenty HD space.
I know this message is somwhat convoluted, but if you can make sense of my issues, pls help.
Regards,
EdincoT
View 1 Replies
View Related
Aug 31, 2007
Hi there
I am running sql 2005, I recently changed my sql login account for security reasons. The site is connecting to the database fine but in my server logs, all the Search engine bots are causing sql login errors. Its like they are still cacheing the old account
Any ideas?
Thanks
Clinton
View 1 Replies
View Related
Jan 14, 2005
Hello all,
Thought I would post here in case anybody can give some information.
Here is the background information:
I have 2 tables (stores and sales) from the Pubs database in Sql Server 2000 copied down to a SQL Server CE database. There is no foreign key/primary key relationship between the 2 tables in the CE database.
Here are the update queries that cause the error:
UPDATE st
SET st.zip = 66668
FROM stores st
INNER JOIN sales sa ON st.stor_id = sa.stor_id
AND st.stor_id = 6380
Update stores SET stores.zip = 55555
FROM sales, stores
WHERE stores.stor_id = 6380
AND stores.stor_id = sales.stor_id
Here is the error message that is generated when I run the query (Param 0 and Param 1 change according to what column and line the FROM clause is in):
Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 1
Param. 2: 0
Param. 3: FROM
Param. 4:
Param. 5:
I ran the 2 queries in SQL Query Analyzer in SQL Server 2000 and they worked just fine. I also created 2 new tables (stores1 and sales1) in SQL Server 2000 using the Select Into clause. The new tables were created from the sales table and stores table in the Pubs database. The new tables had no foreign key/primary key relationship.
I ran the queries again in Query Analyzer against the new tables and the queries produced no errors.
Any suggestions?
Thank you,
Aaron B
View 1 Replies
View Related
Jun 1, 2000
I'm running SQL 7.0 with SP2 applied. I have a database that I'm trying to replicate over the Internet.
I'm having a problem getting the initial snapshot. The total amount of data in the snapshot is 3GB. One .bcp file is 2GB and another is roughly 900MB. There are also a few smaller files.
I have my publisher and distributor setup correctly as well as my subscription. I have tested with smaller datasets. Near as I can tell, there is a problem with my files being too big. I get all the .sch files. The first .bcp file is the 2GB file.
I have tried replicating to another server on my LAN (100base-T) and get the same error.
I don't have the exact error, but it's something like:
'Failed to get file sql01_data1_data120000601105138igfile.bcp'
In my FTP Log, I see that transfer was started then aborted.
Any help on how to deliver large amounts of data over FTP would be greatly appreciated.
Thank you,
Charles
View 2 Replies
View Related
Jul 3, 2007
I have one central publisher server and several remote merge pull boxes. On random remote servers, at random times, replication will fail. The data is inconsistent between the master publisher and the remote merge pull subscribers. (i.e., The subscribers have more recent data than the publisher.)There are no error messages generated, and it's only for one article. The rest seem to update fine.I am a complete noob when it comes to replication. How would I even start troubleshooting this? :S Any assistance is appreciated. Thanks.-D.
View 14 Replies
View Related
Feb 7, 2000
I setup Transactional replication between two SQL Servers.
I am getting the following errors: " Could not find stored procedure 'sp_MSins_stores'." and the job fails.
I dont have any idea why I am getting these errors. I am getting this error for every table in the publication.
Any suggestions are welcome.
Thanks in advance.
View 1 Replies
View Related
Apr 11, 2007
I currently have replication (transactional) set up within my domain successfully using 2000 and 2005.
I need to start replicating to a hosting center for a web app from my server out on the DMZ. I can use SSIS and SMS to connect, update data, etc. but when I try to replicate out there it gives me the following errors:
1. Tells me it needs a named instance so I give it the hosted server name : ABC123
2. Then I get the following:
TITLE: Connect to Server
------------------------------
Cannot connect to ABC123.
------------------------------
ADDITIONAL INFORMATION:
Failed to connect to server ABC123. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
Any help would be greatly appreciated!!
TIA
Tena
View 5 Replies
View Related
Oct 13, 2006
I am investigating the feasibility of a configuration with 3 databases on SQL2005
DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions
DB_B is a subscriber database which subscribes to pub_txn
DB_B is also a publisher which serves up merge publication pub_merge
DB_C is a subscriber database which pulls pub_merge
===============================
Updates on DB_A are successfully replicated to DB_B
Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B
===============================
Updates on DB_B are successfully replicated to both DB_A and DB_C
===============================
Updates on DB_C initially failed with the error
Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.
I then changed the login repllinkproxy to be a db_owner in DB_C
I now get the error
Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.
=================================
I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?
2) Why is update on DB_A not reaching DB_C
3) Why can't I update DB_C?
Any suggestions gratefully received
aero1
View 10 Replies
View Related
Oct 25, 2005
Hi,
I have more than 80 databases on my publisher (SQL Server 2000 SP4), I tried to enable Transaction Replication on all of those databases instantly through some T-SQL programming and DTS Packages. Every thing works fine until the snapshot agents starts to take sanpshot from the publisher databases. As soon as their snapshot agents start for those 80 databases, they start giving the deadlock error. All 80 snapshot agents starts at the same time.
Error Message:
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Error Detail:
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
(Source: Server_Distribution (Data source); Error number: 1205)
---------------------------------------------------------------------------------------------------------------
WHY???
View 2 Replies
View Related
Jun 14, 2015
I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup
The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?
View 3 Replies
View Related
Aug 12, 2015
How to resolve the data inconsistent errors in merge replication ?
View 0 Replies
View Related
Jul 1, 2015
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
View 1 Replies
View Related
Jul 31, 2006
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Thanks,
Lee
View 5 Replies
View Related
Apr 20, 2006
Starwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).
. . . .
. . . .
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"
View 1 Replies
View Related
Aug 27, 2007
Dear Folks:
I am currently engaged in finding the reason why a certain SQL client application (now running under XP SP2) fails when it attempts to query an sql database. This "failure" happens after the nth operation of the same. The application can run anywhere between 2 seconds and 2 hours before it displays a GPF dialog window and is terminated by the user. The application was developed more than 10 years ago using Borland c++ 4.52 (I am using 5.01) and was deployed as a 16 bit windows app. Since then, they've the client) "apparently" had some success running it under Win95/Win98 and 32 bit OSs like W2K and XP.
At this time, I (think I) know that a specific query operation (runing through the KDBF framework) that returns a 13059 BDE code before the returned object is accessed and the KERNAL reports a NULL Handle error. Inside the KBDEF framework, the query function translates to a DbiQExecDirect(,,) function call to the BDE. I have looked at the code and can not find anything wrong.. nor would i expect too. The application worked at one time. But,clearly, something has changed in the environment of both the test system I have, and one installed at a customer site. I have modifed just about every modifiable parameter I can think of. The system in question is an IBM branded machine running XP pro with SP2 applied. I do not know if the app run under XP SP1 correctly, however.
Any suggestions would be greatly appreciated. (i know.. stop using BDE)
Thank you
JRC
View 1 Replies
View Related
Mar 3, 2008
I have a very rubbish connection!! And have been having prodlems with "Protocol error in TDS stream" when trying to save a copy locally through a dts. Basically i have a table with about 11,000 rows, but this table had 28 columns. I've now normalised to a certain extent and now have 2 tables one of 11 columns mostly ints and one of 18 columns, but they will both be excess of 11,000 rows.
So, on to the question ... Will these 2 tables likely ease my network issues... The application works fine on 1 big table. It's just copying to my local machine that's always been the issue since the table hit 10,000 rows
If only I knew what I was doing ... Thanks in advance of any guidence
View 3 Replies
View Related
Jul 20, 2005
Gents,I have come into a system that uses a secondary table to generate (forwant of a better word) Identities.egcreate table myidents( name sysname not null, ident int not null)create procedure getnextident @table sysname, @ident int outputasbeginif not exists (select top 1 1 from myidents where name = @table)insert into myidents values (@table, 0)update myidentsset @ident = ident = ident + 1where name = @tableendnow, (ignoring for now the use of reserved words) the problem is thatthis is called frequently, from other procedures. Trouble is that thecalling procedures call it from within a transaction. We now have awickedly hot spot on this table, with frequent deadlocks.Is there any relatively quick fix for this? Some locking hints orwhatever.Or do we need to go and recode, moving this kind of thing outside thetransaction (which are all rather too long for my liking), and evencosidering using identity columns as a replacement?Thanks
View 4 Replies
View Related
Apr 9, 2007
Hi
I was trying to clean up some conversation in Service Broker and caused alot of blocking that I seem to unable to kill. there was 1 conversation that I was not able to end, so I wanted to restart sql service, But I can't even restart the SQL service. I get the following in Event Viewer
Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_INIT', id 00000001A2B03540, type 2, Task 0x0000000000C2EDA8 : 0, waittime 5400, flags 0xa, owning task 0x00000002DEBCA5C8. Continuing to wait.
Has anyone come across this
thanks
Paul
View 1 Replies
View Related
Aug 8, 2006
I've got a stored procedure and one of the parameters is a DateTime. But no matter what I do to the string that's passed into the form for that field, it doesn't like the format. Here's my code: SqlConnection conn = new SqlConnection(KPFData.getConnectionString());
SqlCommand cmd = new SqlCommand("KPFSearchName", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@DOB", SqlDbType.SmallDateTime);
param.Direction = dir;
param.Value = txtDOB.Text;
// also have tried this:
param.Value = Convert.ToDateTime(txtDOB.Text);
// and
param.Value = Convert.ToDateTime(txtDOB.Text).ToShortDateString;
No matter what I do I always get a formatting error - either I can't convert the string to a DateTime, or the SqlParameter is in the incorrect format, or something along those lines. I've spent a couple hours on this and hoping someone can point out my obvious mistake here...??Thanks for your help!!eddie
View 5 Replies
View Related
May 23, 2007
I got some help on here before with building my query. I thought this was working fine but it turns out when there are multiple records for a column type, it only grabs the first one. I need to get all records. Is there an alternative to MAX? I needed to structure it like this because I needed to return each row as a column and this was the way suggessted before.
My query:SELECT TOP (100) PERCENT PRODUCT_NUMBER, PRODUCT_NAME,
MAX(CASE WHEN ColumnName = 'Federal Specification Number' THEN TheValue ELSE NULL END) AS [Federal Specification Number]FROM (SELECT dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID, dbo.SHARED_FEATURE_VALUES.FEATURE_TEXT_VALUE AS TheValue,
dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE AS ColumnName, dbo.PRODUCTS.PRODUCT_NUMBER, dbo.PRODUCTS.PRODUCT_NAME
FROM dbo.PRODUCT_FEATURE_VALUES INNER JOINdbo.SHARED_FEATURE_TYPES ON
dbo.PRODUCT_FEATURE_VALUES.FEATURE_TYPE_ID = dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE_ID INNER JOINdbo.SHARED_FEATURE_VALUES ON
dbo.PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = dbo.SHARED_FEATURE_VALUES.FEATURE_VALUE_ID INNER JOINdbo.PRODUCTS ON dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID = dbo.PRODUCTS.PRODUCT_ID
UNIONSELECT dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID, ISNULL(dbo.EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE,
dbo.EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE) AS TheValue, dbo.EXTENDED_ATTRIBUTES.COLUMN_NAME AS ColumnName, PRODUCTS_1.PRODUCT_NUMBER, PRODUCTS_1.PRODUCT_NAME
FROM dbo.EXTENDED_ATTRIBUTE_VALUES INNER JOINdbo.EXTENDED_ATTRIBUTES ON
dbo.EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = dbo.EXTENDED_ATTRIBUTES.EXT_ATT_ID INNER JOIN
dbo.PRODUCTS AS PRODUCTS_1 ON dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID = PRODUCTS_1.PRODUCT_ID) AS t1
WHERE PRODUCT_NUMBER = '02083'
GROUP BY PRODUCT_NUMBER, PRODUCT_NAME
ORDER BY PRODUCT_NUMBER
This returns:
Product_Number Product_Name Federal Specification Number
02083 Di-Electric Grease, 10.5 Wt Oz FDZ-CFR-21-178.3570
There is another record for Federal Specification Number I need to return as well. If I change to MIN, it gets the other record. Anyway I can get both?
View 15 Replies
View Related
Jun 11, 2008
I have my SQLDataSource configured as shown in the picture. I ran the Execute Query and input an ID I know is in the database and it returned nothing. I ran into this probelm on another part of my site and i got it working by using 2 SQLDataSources, but im trying to keep the amount of code down.
http://junk.icore-studios.com/junk/Codeissues/Innerjoins.JPG
Any ideas on why it would be behaving this way?
View 2 Replies
View Related
Aug 2, 2000
I seem to be having a problem on all of my SQL servers. WHen I or a developer attmept to do a delete on a table i get a Log file for database is full. I truncate the log try again and get the same error. IT doesnt seem to matter how much is being deleted or how big the table is. THis is very strange and very frustrating.
Thanks
David
View 5 Replies
View Related
Jul 5, 2007
Here's my basic syntax:
Code:
select
dma.dma_market_area,
count(fc.contactid) as Number_Of_QCs,
(case when fc.ctca_currenttier = 1 then count(fc.contactid) end) as P1,
(case when fc.ctca_currenttier = 2 then count(fc.contactid) end) as P2,
(case when fc.ctca_currenttier = 3 then count(fc.contactid) end) as P3,
(case when fc.ctca_currenttier = 4 then count(fc.contactid) end) as c1,
(case when fc.ctca_currenttier = 5 then count(fc.contactid) end) as c2,
(case when fc.ctca_currenttier = 6 then count(fc.contactid) end) as c3
from ...
And then I get output like this:
dma_market_areaNumber_Of_QCsP1P2P3c1c2c3
ALBANY-SCHENECTADY-TROY66NULLNULLNULLNULLNULL
ALBANY-SCHENECTADY-TROY1NULL1NULLNULLNULLNULL
ALBANY-SCHENECTADY-TROY1NULLNULLNULL1NULLNULL
ALBANY-SCHENECTADY-TROY1NULLNULLNULLNULLNULL1
How can I get there all to group to one row, based on the first column?
View 1 Replies
View Related
Apr 17, 2006
I have been having problems inserting data with dates.
If I do not use a constraint then the data is inserted without a problem but if the constraint is added I get this error.
Code:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"
What is the problem here?
I have the logins set to British English
View 3 Replies
View Related
Aug 8, 2004
We are trying to deal with replication in a legacy design involving 2 SQL servers each taking INSERTS from about 100 call centre client PCs. In each case a client logs into either SQL Server and upon each INSERT is handed a unique Call_ID to use when inserting additional information in relation to that specific call.
Each of the two databases are subsequently being replicated into a third database where reports are being pulled.
The problem is that to prevent each database giving the same Call_ID to a client we have setup SQL 1 to use a Call_ID starting with 1 and incremented by 2 (i.e odd numbers!). SQL 2 starts with 0 and increments by 2, (even numbers). These ‘increment’ rules are built into the table schema and seem to be causing a problem when we try to replicate into the third database as the two initial schemas are not considered identical.
The first database to be replicated will work and the second will fail. We get messages saying it is due to unique values.
I thought we may be able to have identical schemas by changing the ID field to a fixed 12 digit number and prefix it with a 10xxxxxxxxxx on one server and 11xxxxxxxxxx on the other. The 10 and 11 would be held in a table with the value being pulled based upon server name.
ServerValue
SQL110
SQL211
Hence we would be able to extract the value and prefix the ID with it.
Has anyone come across the reason as to why the first replication will work but the second will always fail? And would this mod solve the problem?
Moreover I suspect that our design is fundamentally flawed and that we need to have two servers handling a single database? This single database would then more easily be replicated to the reports database.
Thanks for any input!
Paul
View 1 Replies
View Related
Sep 21, 2004
I'm maintaining a database with an automated backup process.
Periodically, it does a "BACKUP DATABASE" to a local file and then do a file copy across the network to another system. This file is 2.7GB and when it is being copied it causes network problems; specifically ADO connections to the database get broken.
Does anyone have experience with this kind of issue?
View 8 Replies
View Related
Feb 18, 2004
Hi everyone! I'm new to this forum and I suspect I'll be using this forum frequently. Good stuff.
Allow this question may appear to be Web-related, I think the problem is with what I'm doing with the database. Please read.
I'm trying to implement a page tracking solution using ASP and SQL 2000. It basically writes a new record to a table every time a user visits a page on the site. It appeared to work fine at first, then I've increasingly been getting time out errors on my pages -- all pointing to the include file that fires the database write.
Here's the code that's referenced on every page:
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "dsn=x;uid=y;pwd=z;"
Set objRecordset1= Server.CreateObject("ADODB.Recordset")
objRecordset1.Open "SELECT * FROM table",Conn,1,2
objRecordset1.AddNew
objRecordset1.Fi elds("PAGE") = Left(request.servervariables("SCRIPT_NAME"),100)
objReco rdset1.Fields("QUERY_STRING") = Left(request.servervariables("QUERY_STRING"),100)
objRec ordset1.Fields("DATE") = Date()
objRecordset1.Fields("TIME") = Time()
objRecordset1.Fields("PLATFORM") = Left(request.servervariables("HTTP_USER_AGENT"),100)
obj Recordset1.Fields("REFERRER") = Left(request.servervariables("HTTP_REFERER"),100)
objRec ordset1.Fields("USER_IP") = Left(request.servervariables("REMOTE_ADDR"),20)
If Request.Cookies("TEST")("ID")<>"" Then
objRecordset1.Fields("VISITOR_ID") = Request.Cookies("TEST")("ID")
End If
objRecordset1.Update
Conn.Close
Set Conn=Nothing
%>
After taking out the reference to the above code everything speeds back up. So, I know the performance hit and time out issues have to do with the code above.
Is it the simultaneous write to the table, the constant opening and closing of the recordset, the cursor type, the lock type – or combination of things?
HELP!! Thanks!
David
View 3 Replies
View Related
May 27, 2008
Hi, I am a new learner and user to SQL Sever 2005 and am having some major frustration trying to write a simple query.
I have two tables, 1) Ticket_Purchase, 2) Flight.
The Ticket_Purchase table has these columns: Ticket_Purchase_Number(PK), Flight_Number(FK), Date_Purchase_Made, Ticket_Price, Class_of_Ticket, Passenger_ID
The Flight table has these columns: Flight_Number(PK), Flight_Date, Flight_Departure_Time, Flight_Arrival_Time, Flight_Origin, Flight_Destination
I am trying to create a query that will tell me: On which flight were the most first class tickets sold?
There are only two types of classes; 'E' for economy and 'F' for First Class.
So far I am able to get a list of all the First class flights for each flight and can visually see which flight has the most first class tickets by counting them manually on the report generated, but I am totally confused on how to simply pull the single flight with the most First class tickets sold. I wonder if this requires something more like a join or a nested sub query?
The SQL I wrote for the above is:
Select Class_of_Ticket, Flight_Number
From Ticket_Purchase
Where Class_of_Ticket = ('F')
Order By Flight_Number;
And it produces:
Class_of_Ticket Flight_Number
--------------- -------------
F 1
F 1
F 1
F 2
F 2
F 3
F 3
F 3
F 3
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 5
F 5
F 6
F 6
F 6
F 7
F 7
F 8
F 8
F 8
F 9
F 9
F 9
F 9
F 9
(38 row(s) affected)
Rather I would like it to produce:
First_Class_Seats Purchased Flight_Number
--------------------------- ------------
14 4
I hope I didn't make this to confusing to understand as I am still learning the syntax and 'lingo' of how to communicate this stuff verbally.
Thank you for any help you could offer. It would be much appreciated.
Edit: the query report I pasted from SQL should have the flight number directly under the column header. For some reason the space between Class and Flight_number is being eliminated in the post.
View 5 Replies
View Related
May 30, 2008
Hi,
I am getting odd result while executing the below query.
SELECT COUNT(DISTINCT(D.image_id)),
(CASE WHEN D.stage_id IN (SELECT SS_STAGE.stage_id FROM SS_STAGE WHERE SS_STAGE.STAGE_ID = D.STAGE_ID )
THEN (SELECT SS_STAGE.STAGE_ID FROM SS_STAGE WHERE SS_STAGE.STAGE_ID = D.STAGE_ID)
ELSE D.stage_id
END) stage_id
FROM deadline D, OCCURRENCE O
WHERE O.image_id = D.image_id
AND (D.APPROVED_STAGE IS NULL OR D.CONFLICT = 1)
AND D.LOGON = 'pbitest2'
AND O.delete_ind = ' '
GROUP BY stage_id
My actual requirement is to group by using the alias name.
This query getting the results by grouping the STAGE_ID from DEADLINE table!!!.
Please help me on this...Thanks in Advance.
Sudheer
View 5 Replies
View Related
Mar 16, 2007
I have detached a SQL Server 2005 database from one server and attached it to another SQL Server 2005 and I now get the following in the error Log and in the event viewer evry 10 - 20 minutes or so.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-03-16 12:37:14.64 spid17s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-03-16 12:37:14.64 spid17s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Starting up database 'DBName'
It appears under different SPID's 18, 20, 24 ...... and consistently has the 4 errors in a row
Full Text indexing is running for both servers but I dont know if this is the cause of the error.
I would greatly appreciate any help to get rid of this as I have trawled the net and not found anything of use.
Thank You.
View 8 Replies
View Related
Dec 13, 2007
Hi i have a cursor in a Stored Procedure. The problem is that it's poiting to the first row and causing an infinite loop on it.
How can i stop this and make it go to all rows. Here is my code.
Declare @CountTSCourtesy int
Declare @WaiterName nvarchar(100), @CursorRestaurantName nvarchar (100)
Declare waiter_cursor CURSOR FOR
SELECT new_waiteridname, new_restaurantname
FROM dbo.FilteredNew_CommentCard
Where new_dateofvisit between @FromDate and @ToDate and new_restaurantname = @Restaurant
Open waiter_cursor
FETCH NEXT FROM waiter_cursor
into @WaiterName,@CursorRestaurantName
While @@FETCH_STATUS=0
BEGIN
Exec WaitersCountExCourtesy @WaiterName,@CursorRestaurantName
END
Close waiter_cursor
Deallocate waiter_cursor
END
Thanks in advance...
View 1 Replies
View Related
