Appointments - There are 3 appointments in this table, which includes a 'date' field
Notes - There are 2 notes in this table, which includes an 'appointment' field to relate the notes to their respective appointments and a 'new' field which shows that they have been read (or not).
The draft SQL below is designed to return all PAST appointments that have either unread notes (where new=1) or no notes at all.
SELECT appt.ref FROM (SELECT DISTINCT ref FROM Appointments WHERE DATEDIFF(day, date, '15 JUN 2004')>0) appt, Notes note WHERE (note.appointment=appt.ref AND note.new=1) OR NOT EXISTS(SELECT 1 FROM Notes note2 WHERE note2.appointment=17)
There are NO NOTES with '17' in the appointment field and NO NOTES with new equal to 1, so the SQL should return 3 results. I've used 17 just to test it.
For some reason, the SQL returns 6 results, even though there are only 3 appointments in the database.
I have a package that when I open it up, gives me this error:
Error 1 Error loading myPackage.dtsx: The connection "{AEDA784A-4076-4661-BC99-B4D819E0C21B}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
However, I don't know what this error is possibly referrring to. All of my tasks, where needed, have the appropriate ole db connection and are NOT pointing to this non-existent connection. I deleted all connections except the one I use.
I addition, none of my tasks are showing ANY errors whatsover and package executes perfectly.
How do you avoid phantom inserts? Can you recommend a strategy (or other posts, articles) to avoid "phantom inserts" with the default SQL Server isolation level ("Read Committed").
I am seeing something strange. I have a data file that has 6 rows that looks like:
ABC, "OPENING BALANCE", 1234, etc
ABC, "CLOSING BALANCE", 1235, etc
ABC, garbage data, etc
XYZ, "OPENING BALANCE", 1234, etc
XYZ, "CLOSING BALANCE", 1235, etc
[][] -- weird box things that shows up in my flat file conn mgr
I have a script transformation that reads the incoming rows as a single line, then checks for the value of the row.line, whether it's "OPENING BALANCE", or "CLOSING BALANCE". It ignores all other lines.
I even added a message box that pops up when it finds "OPENING BALANCE" or "CLOSING BALANCE". It only pops up 4 times, like it should.
However, when I check the database, it has 6 rows! The 4 good rows are there, and 2 garbage rows with a bunch of NULLS in them.
I really don't understand how this is happening. Please, any ideas.
We are running two NT 2000 servers with two synchronized databases (DB1 as publisher, DB2 as subscriber), MS SQL 7, and IIS (ASP). Synchronization is done by immediately transactional replication. We have a radware switch ( www.radware.com ) between these two servers for load balancing and redundancy issues. The problem is folowing - when I open two windows (connections to database DB1 - publisher) and run the same SQL select command I get two different recordsets (differency is 50% or more in count record rows). Any ideas how to solve this problem?
I think probably is this problem somehow tied with second: error message for stored procedures running from ASP: -2147467259, [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa' '. SQL : exec business_update @BusPrimId='1230138980', @business_name='Software-test', @Contact_Name='Brian'
A fellow developer of mine has created a ASP.Net application that executes some fairly complex stored procedures. This application is for all intensive purposes a queue and 3 or 4 people work on processing items in a FIFO type environment. There is logic in the Stored procedures to make sure each worker get a unique entry using a series of query hints.
Well what we are seeing is if these works are moving at a rapid pace and we execute an sp_who2 on the sql server there are entries that that seems to be hanging there and REMAINING there even after a browser is closed or the disposed method has been called on the connection object. Has anyone else experienced something similar to this with an ASP.Net application used by mutiple people?
My inclination is to blame the design of the application, but before I do that and step on my co-workers toes I thought I would throw this out to the group.
I have a sp that when executed inserts data into two tables(shown below). The sp works fine when the correct information is inserted into the tables but when you try and insert data that breaks the constraints in the table it obviously errors, but it seems to inert a new row in to the tmptimesheet table(error message shown below). When you open the tmptimesheet table there isn’t the row of a data there but if you run this (SELECT IDENT_CURRENT('tmptimmesheets') after the error it will come back with a id one higher than what’s in the table. Then the next time you run the sp a record will be inserted into the tmptimesheet table and not in to the tmptimsheethours table!? I’m at a total lost at what to do can someone please help!?
CREATE TABLE [dbo].[tmptimesheets]( [TimesheetID] [int] IDENTITY(1,1) NOT NULL, [Placementid] [int] NOT NULL, [Periodstarting] [datetime] NOT NULL, [createdon] [datetime] NOT NULL, [createduserid] [int] NOT NULL, [Issued] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, [ReadyForBilling] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, [Reject] [nchar](1) COLLATE Latin1_General_CI_AS NULL, [Comments] [text] COLLATE Latin1_General_CI_AS NULL, [Rate] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, CONSTRAINT [PK_tmptimesheets] PRIMARY KEY CLUSTERED ( [TimesheetID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [IX_tmptimesheets_1] UNIQUE NONCLUSTERED ( [Placementid] ASC, [Periodstarting] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[tmptimesheethours]( [TmpTimesheethourid] [int] IDENTITY(1,1) NOT NULL, [Timesheetid] [int] NOT NULL, [applicantid] [int] NOT NULL, [Workedon] [datetime] NOT NULL, [Hoursworked] [numeric](10, 2) NOT NULL, [performancevalueid] [int] NOT NULL, [Reject] [ntext] COLLATE Latin1_General_CI_AS NULL, [Breaks] [numeric](10, 2) NOT NULL, CONSTRAINT [PK_tmptimesheethours] PRIMARY KEY CLUSTERED ( [TmpTimesheethourid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO USE [Pronet_TS] GO ALTER TABLE [dbo].[tmptimesheethours] WITH NOCHECK ADD CONSTRAINT [FK_TimesheetHours_Timesheets] FOREIGN KEY([Timesheetid]) REFERENCES [dbo].[tmptimesheets] ([TimesheetID]) GO ALTER TABLE [dbo].[tmptimesheethours] CHECK CONSTRAINT [FK_TimesheetHours_Timesheets]
Error Message Msg 2627, Level 14, State 1, Procedure sp_tmptimesheet_insert_day, Line 40 Violation of UNIQUE KEY constraint 'IX_tmptimesheets_1'. Cannot insert duplicate key in object 'dbo.tmptimesheets'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 65 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 86 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 108 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 130 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 153 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 178 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 200 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 3902, Level 16, State 1, Procedure sp_tmptimesheet_insert_day, Line 223 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Hi All,Have come across something weird and am after some help.Say i run this query where rec_id is a column of table arlhrl,select * from arlhrl where rec_id >= 14260This returns to me 2 records with rec_id's of 14260 and 14261Then I run this queryselect * from arlhrl where rec_id >= 14263This returns 7 records with rec_ids of 14263 up.How come the first query doesn't return the records returned by the2nd query also?If I select for 14262 no records are returned. It is like this is aphantom record or has an end of file character in it.I tried re-creating the indexes but to no avail. If anyone has anyideas about what could be causing it or how to fix it it would be muchappreciated.Thanks in advance,Andrew
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
Hi All, I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Hi, I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number(). I would like to give my users to option of removing individual people from this list but cannot find a way to do this. I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message. Is there any way to do this? I am still new to stored procedures so any advice would be helpful. Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
Hi, i wanted to know if there is a way that we can know if Sqldatasource retrieved 0 results, i wanted that cause i want to make a condition that if 0 results retrieves a page with the text "No news"...Thanks in advance.
I have some SP's I run once a month and each SP takes a few mins to run and when I batch em together in one shot, I hate sitting there waiting for them to finish whats the easiest way to report back the status of the exec?
I have two tables which are info and pubssubcribe. each record on the info table has a corresponding subscription on the pubssubscribe table. I need to extract those records on the pubssubscribe with the infid appearing more than once and having the pubid BETWEEN 19 AND 22 and count the records grouped on infid couldn't get thru with the code below
Here's my sql code SELECT info.infid, info.infname FROM info INNER JOIN pubssubscribe ON info.infid = pubssubscribe.pubinfid WHERE (info.infcond IS NULL) AND (pubssubscribe.pubid BETWEEN 19 AND 22 AND pubssubscribe.pubid > 1) AND (info.infid > 1) ORDER BY info.infid
How could I export results into a text file? Have been copying and pasting results into excel, but have been getting memory errors, whcihw e are working on fixing. In the meanwhile, I'd like to send these results (about 200,000 rows)into a .txt file as I execute teh query.
Hi can anyone help me maybe im just being thick but i cant for the life of me work out how to get the top ten results based on the highest number within a column
ie.
top2 would be
name1 4 name2 8 name3 102 name4 113
i want name3 and name4 to be returned
SIMPLE well it should be aRRRRGGG HELP!!!
i have tried using max but that only returns the highest how i get the next highest etc i dont know
Hey guys, I have a small issue that I'm not sure how to solve. I have 2 tables that I'm working on, that has the UserID, LastName, FirstName, DocumentDesc. Each DocumentDesc has its own DocumentTypeID
1st table is called Person Fields(UserID, LastName, FirstName)
2nd table is called Documents Fields(UserID, DocumentTypeID, DocumentDesc)
The query that I have for this is the following:
Code Snippet Select a.UserID, a.LastName, a.FirstName, b.DocumentDesc From person a Join documents b on a.UserID = b.UserID Where b.documenttypeid = '126d2beb-f7a1-4bf1-b9c0-dded37d3a6bc' OR b.documenttypeid = '9087956e-1fb0-4f3d-ba33-ef31d79141af' Order by LastName
The first DocumentTypeID is for RESUME and the Second one is for TRANSCRIPT
This is a sample from the query above
UserID LastName FirstName DocumentDesc 1 Smith Paul Resume 1 Smith Paul PhdStatistics 1 Smith Paul MS Applied Statistics 1 Smith Paul MS Operation Research 2 Jackson Jane Resume 2 Jackson Jane MS Information Systems
What I'm trying to do is get this in on one line like so:
UserID LastName FirstName DocumentDesc DocumentDesc DocumentDesc DocumentDesc 1 Smith Paul Resume PhD Statistics MS Applied Statistics MS OperResearch 2 Jackson Jane Resume MS InforSystems
Note: Not all names have the same amount of documents.
Hi, is it possible to make an sql query that has an Outer Join but return only one row of results max per id. For example i have an Articles table, and a PicturesForArticles table. The Articles table has an id field(aid), a title field(aTitle) and a content field(aContent). And the PicturesForArticles table has an id field(pid), a PicPath filed and a field linking it to the articles table(aid) Obviously the PicturesForArticles field stores pictures for the articles, and article can have a multiple number of pictures, or no pictures at all. So i want to make a query that will return all of the Articles fields and a picture for each article. Even if the article has many pictures i only want to get a single row for each aid(Articles Id), and if there are no pictures for that article the picture fields will be null. Is there any way to do this, to only return on row of results for each aid? Thanks
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters></asp:SqlDataSource> any help would be appreciated
I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValueSo the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql. So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.
Hi all, I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ? SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1 FROM dbo.Orders WHERE (ShipVia = 1) GROUP BY EmployeeID ORDER BY EmployeeID
Table 1 Results EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29 SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2 FROM dbo.Orders WHERE (ShipVia = 2) GROUP BY EmployeeID ORDER BY EmployeeID Table 2 results EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19 Table 3 the desired result: EmployeeID CountShipVia1 CountShipVia2
I am trying to create a user permission system that is stored in a database. What is the best table structure for accomplishing this? How could I display the permissions in a grid? Currently I have a users table and a permissions table. I created a map between the two. However, I don't see how this allows me to display a grid. All my "columns" for permissions are actually rows from the permissions table. So ideally my grid would look something like this. User | P1 | P2 | P3 |A | T | F | T |B | T | T | T |Thanks for any help. I am relatively new to SQL so please explain gently.
I have the following stored procedure that is returning nothing can anyone please help? SELECT job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date, customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM tblOrderWHERE (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End) When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id
Hi, Based on the "SQL Book Server Online" from MSSQL 2000 I wrote the following codes in order to export the search results in XML format. se pubsselect 1 as tag, null as parent, stor_id as [store!1!stor_id], stor_name as [store!1!stor_name], null as [Order!2!Ord_Num], null as [Order!2!ord_date] from storesunion allselect 2 as tag, 1 as parent, sa.stor_id, null, sa.ord_num, sa.ord_datefrom sales sajoin stores ston sa.stor_id = sa.stor_idorder by [store!1!stor_id], [Order!2!Ord_Num]for xml explicit The problem is that I want to results to be something like: <stores> <store> <stor_id>6380</stor_id> <stor_name>Eric the Read Books </stor_name> <order> <ord_num>A2976</ord_num> <ord_date>1994-09-14 00:00:00.000</ord_date> </order> <order> <ord_num>722a</order_num> <ord_date>1994-09-13 00:00:00.000</ord_date> </order> </store> <store> <stor_id>7066</stor_id> <stor_name>Barnum's</stor_name> <order> <ord_num>6871</ord_num> <ord_date>1993-05-24 00:00:00.000</ord_date> </order> <order> <ord_num>QA7442.3</ord_num> <ord_date>1994-09-13 00:00:00.000</ord_date> </order> </store></stores> How can I get the results in this format? And, also, how can I export them directly into an xml file and save it on the disk? Thank you in advance for your answers.
I was wondering if there was a keyword that would allow you to return the number of results from a query such as (this is fake just giving an example so that someone can give me the real answer)
