I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a boolean
field used for the check box.
Since the local table used to contain the boolean field is local to the MDB
file, the result is a heterogeneous join in the underlying form query, which
degrades performance. I would like to have the entire query be based on back
end SQL data. However, each user needs to be able to make a unique set of
selections, without other users' selections affecting theirs.
An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined to
the selections table; link the view to the front end; and base the form on
the SQL: "Select * From MyView Where MachineName='MyMachine'".
However, I wonder if there's a better approach. Any ideas would be
appreciated.
now i need to create a proc that runs through DatabaseA gets all the tables, creates DatabaseC and insert all the Tables,Procedures, and data when the user creates a new company in our software interface.
if you could just help me cycle through the objects i can manage the rest.
ok, i'm building a page to display a list of courses, a user rating and 'last visited' date.
I have 3 tables - course (a list of all courses) review (a list of all ratings) visit (user visits to each course)
I've put together an SQL statment that returns everything i need, however its not quite right. SELECT course.courseID, course.courseName, course.courseURL, avg(review.fldRating) AS fldAverage, visit.visitDate FROM course
LEFT OUTER JOIN review ON course.courseId = review.fldcourseId
LEFT OUTER JOIN visit ON course.courseId = visit.courseId and visit.userId = 2
GROUP BY course.courseId, course.courseName, course.courseURL, visit.visitDate ORDER BY course.courseId, visit.visitDate DESC
The problem lies with the fact that each time a user enters a course a new record is inserted into the visit table - so the visit table will show how many times a user has entered a course and on which dates.
because the user has entered course 1 twice, the list is now showing 2 course1's - how can I change the statemtent to only select the most recent user visit, but still keep the complete list of courses?
I'm a bit of an SQL novice, so appologies if I've not explained this very well, Thanks in advance,
Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column
for example:
table 1
Alan Andrews 1 main st 07465 John Andrews 1 main st 07465 Erick Andrews 1 main st 07465
I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.
So is there a way of doing this in SQL?
It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.
I have a webform that lists all items (codes) on the left and selected items (codes) on the right. A user selects an item on the left and clicks a button to move it to the right side. An update changes the bit from 0 to 1. This uses the bit column in the table to determine what is listed on the left (0) or right (1) sides. Then I can filter in my stored procedure on the bit column WHERE (dbo.tblCodes.CodeSelect = 1) My problem with this is that if two or more users are doing this process on different sessions, they can trip over each others selections. I'm hoping someone has a suggestion on how I might avoid the users having this problem.
I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type): ****************************************************************** SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1" WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999')) GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"
********************************************************************** Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)
I created a package using the import/export wizard in SSIS, that loads data from one database to the other. I am trying to find out how I can add and remove the tables that were originally selected when the package was created. I opened the package in BIDS, and I could not find that particular option. I know you can do this in 2000/DTS...
Don't think this can be done, but a user request, so thought I should ask anyway...
I have report with 11 filters (yes, I know thats overkill, but we aren't using analysis services) and the user has asked, if he wants to run it with the same selection criteria every month, can he do that without having to re-select them all?
Hello All,I'm not really sure where to post this as I'm not quite sure how to approach the problem; either whether it's an SQL problem or shoudl be addressed on the form.Anyway, on an .aspx page (using VB.NET) I have a drop down box and two text boxes. The user first selects a centre from the drop down and then enters a start and end date in the two text boxes. The user then clicks on a button which lists the results of a SQL server stored procedure using the specified parameters in a datagrid. All this is fine and works.Users however have requested an 'All centers' option in the drop down, which if selected, essentially means that instead of listing the results for a single centre, all centres are listed for the start and end dates specified.This is where I'm having problems. How do I feed this into my stored procedure? Is it a change in the procedure or something I need to do on the form?Any help appreciated.MoP.S. stored procedure look like this:------------------- @centreid int, @startdate varchar(20), @enddate varchar(20) ASSELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@startdate) = 0 OR CentreID = @CentreID)AND (DataLength(@startdate) = 0 OR datecreated >= @startdate)AND (DataLength(@enddate) = 0 OR datecreated <= @enddate)
Anyone know if it is possible to limit the number of selections in a multi value parameter? Eg: There are 50 values in the drop down combo, but I want the user to be able to select a maximum of 10?
I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:
CREATE FUNCTION dbo.SplitOrderIDs ( @OrderList varchar(500) ) RETURNS @ParsedList table ( OrderID int ) AS BEGIN DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ',' SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1))) IF @OrderID <> '' BEGIN INSERT INTO @ParsedList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion END SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos) SET @Pos = CHARINDEX(',', @OrderList, 1)
END END RETURN END GO
I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.
Any suggestions?
Thanks
My plan is to have the same ability as under the 'Optional' section of this page:
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
I have a report that includes two multi-valued parameters. In the Default Values section, I choose 'from query' and select dataset and value field. In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field. When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected). How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
I am designing a report which will be used under the subreport control of a master report, now i am using a stored procedure for the subreport which has two parameters.
In the subreports dataset parameters tab, how can i mention the master reports columns, since i do not see the master reports columns here. since it is a different report.
If i use a hardcoded select query then i can just say
select address, city, state, zipcode from customer where (customerid = @customerID)
but i am using a stored proc. how to define the parameters under datasets parameter tab.
Does anybody know of a way to rollback SQL Server 2005 databases back to SQL Server 2000? Is there a way of doing it without resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I want to rollback to SS 2000, which was a lot more stable. I am having several major issues that are affecting my whole company's day-to-day operations and the managers are not happy. Some of the issues include night time batch running very sluggish for no apparent reason. This is a biggest problem because it only occurs once or so a week and causes a disturbance with the daily activities when the night time processing isn€™t completed on time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I don't believe it is a matter of my application needing to be retuned because if that was the case, then why isn't it running sluggish every night? Also, it's never the same day that the sluggish behavior occurs. If it was occurring on the same night, then I would have something to investigate within our application, but it doesn't. Another issue that I am having involves a night time job that restores a copy of the production database to the Data Warehouse server to be used for updating the data warehouse. Again, most of the time it runs great (~2 1/2 hours), but once or twice a week, it goes stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended state. What's a suspended state and how can I get it out of suspended state? I can find no information about suspended state in BOL. I did a Google and nothing came up. If this suspended state was put in for security reasons, great, but then tell me how I can remove the suspended state. I am also not happy with the fact that I can't get accurate information about the queries that are actively running at that particular moment. In SS 2000, when I noticed high CPU usage on the server, I would run the sp_who2 active stored proc and it would show me all the active thread and how much CPU it was consuming. I would then find the running threads with the highest CPU numbers and investigate the query and see if we could improve it. Now in SS 2005, I get in the same situation and run the sp_who2 stored proc, and there is no smoking gun. All of the active threads are showing very little CPU usage, which I am very suspect of. What the heck happen to sp_who2? I looked at some of the other ways of looking at running processes (i.e... sys.sysprocesses) and they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of years before I upgrade to SS 2005.
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ?
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
hi team, .Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today. Here is the code
Code Snippet create procedure SearchPartKeyword ( @Keyword varchar(250) = null, @Universal_Id varchar(10) = null ) as select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p inner join notes n on p.PartNumber = n.Identifier where n.Universal_ID = @Universal_ID and p.Description like @Keyword union select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p where p.Description like @Keyword
and the results: PartNo Description SO Notes LA DMM Legacy Folder Printer de90008 MAIN BOARD 1 DGF1 114688 0 0 0 de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
create table Test ( [recId] [int] identity(1, 1) not null, [code] [varchar](50) not null, [prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)), constraint [PK_Test] primary key clustered ( [recId] ) with fillfactor = 90 on [primary] ) on [primary] go
insert into Test (code, prime) values ('AVA', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('CAR', cast(1 as bit)) insert into Test (code, prime) values ('CAR', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(1 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit))
select * from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail insert into Test (code, prime) values ('RLW', cast(1 as bit))
anyone try to convert a 7.0 database back to 6.5? Is there a way to move the data back or does the 6.5 to 7.0 upgrade change field identifiers or anything else prohibiting the move?
