i have a table FORUM_REPLY it contain follwing fields
quest_id,answer_id, reply_user_id.
i want who is post most answer.
so i need reply_user_id and max(no_of_answer).
but following query given reply_user_id and no_of_answer only
SELECT reply_user_id,count(answer_id) as no_of_answer FROM FORUM_REPLY GROUP BY(reply_user_id)
How to get max_no_of_answer?
Hi all,
I am new to the DTS game - or at least to trying to do anything other than transfer rows between databases with identical structures.
I now need to create a far more complex DTS package to transfer data between an SQL Server database (I am using SQL Server 7.0) and an Oracle database. I have no trouble in making the connections and performing simple DTS's beween the two.
However, my current task is more complicated.
Problem 1:
In one database I would store fifty values as 5 five records each containing 10 values (i.e. has ten fields). However, in the other database these fifty values would all be stored as one record (i.e. one row with 50 fields). How could I go about creating a DTS to transfer this information?
Problem 2:
These values should only be transfered if certain conditions are met in an another unrelated table. For example, a flag in another table indicating that transfer of the said values should occur.
Any help with either of these problems would be much appraciated.
Thanks in advance,
Ross
When one of the users log into the database via a thrid party application
they receive an error message:
2000/11/16 10:40:13.84spid51Error : 605, Severity: 21, State: 1
2000/11/16 10:40:13.84spid51Attempt to fetch logical page 7832 in database 'highview' belongs to object '1241055457', not to object 'application_data'.
2000/11/16 10:45:07.68spid46Getpage: bstat=0x1008/0, sstat=0x80002110, disk
2000/11/16 10:45:07.68spid46pageno is/should be:objid is/should be:
2000/11/16 10:45:07.68spid460x1e98(7832)0x49f900e1(1241055457)
2000/11/16 10:45:07.68spid460x1e98(7832)0x4810b86f(1209055343)
2000/11/16 10:45:07.68spid46... extent objid 0, mask 0/0, next/prev=0/0
2000/11/16 10:45:07.68spid46... retry bufget after purging bp 0x2da7e060
I tried running DBCC checkdb, newalloc,& checktable to fix the probelm and than droping the table and rebuilding it but not success, any suggestion are
more than welcome.
If anybody can suggest the most efficient way to "page" the output from a big and wide table (about 7000000 records) in order to display on the web in the user requested sort order. Sort order could be on at least 7 different columns from 50 of returned.
I need to find a solution to move to the next and previous page.
Any Idea?
Dim
what if I took this trigger and based it on a view rather then a table
CREATE TRIGGER TerminationUpdateTrigger ON EmployeeGamingLicense
FOR UPDATE
AS
INSERT INTO TERMINATION(Status,[TM #],LastName, FirstName, [SocialSecurityNumber], DateHired, Title)
SELECT STATUS, [TM#], LASTNAME, FIRSTNAME, [SSN#], HIREDATE, JOBTITLE
FROM Inserted
WHERE STATUS = 'TERMINATED'
CREATE VIEW dbo.Update_Terminations
AS
SELECT STATUS, TM#, LASTNAME, FIRSTNAME, SSN#, HIREDATE,
JOBTITLE
FROM dbo.EmployeeGamingLicense
WHERE (STATUS = N'TERMINATED')
Base the Trigger on this View rather then on the Table itself????
Hi I'm new to sql and it would be great if someone could give some idea on how to do the following
This is the relational model:
Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(PaNum, AcNum)
Field(FieldNum, ID, Title)
Interest(FieldNum, AcNum, Descrip)
The question is :-
Which academics have the largest number of fields of interests? Return their academic number,
given name, family name, institution and total number of fields they are interested in. Your must use a
subquery in the sense that you must use 2 sql statements and use any of the following to connect both:-
1)Exists
2)Not exists
3)IN
I have a table call CLIENTE and another table call ENDERECO where the CLIENTE table has the FOREIGN KEY of the ENDERECO table.
When I try INSERT COMMAND in C# this message is show.
The instruction INSERT conflicted with a constraint of FOREIGN FUNDAMENTAL "FK_CLIENTE_ENDERECO." The conflict happened in the database "E:ARQUIVOS DE PROGRAMASMICROSOFT SQL SERVERMSSQL.1MSSQLDATALINETEC.MDF", table "dbo.I ADDRESS", column 'IDENDERECO.'
The instruction was concluded.
I am a "little" lost.
Thanks.
Hi there,
I have number of tasks in my control flow most of them are execute sql task. I want to update one of the column in my table when anyone of the task in the control get fails?
Please let me know if anyone have an idea how to do this.
Thanks and Regards
Lets say I have to insert 60,000 or so records into SQL Server from another data source using the sqlTransaction class.
However if at any point an error should occur I would like to roll back any changes.
Would a transaction be a bad idea when dealing with this many records?
Thanks for any advice.
SA
Dear Friends,I'm a junior DBA,
I've to prepare an online examination.
for this, I've three categories.
a)beginer level
b)intermediate level
c)expert level
again here subjects are 6. like sqlserver,oracle,c#,vb.net,html,javascript.
in these subjects, i've to select these three types of questions.
now how can i design for this requirement? shall i create three tables for beginer, intermediate,expert or shall i create 6 tables and write according that?
am i given correct inputs?
please give me an idea to design
thank you verymuch experts.
Vinod
Hi guysWe have a following problem. For security reasons in each table in ourDB we have addition field which is calculated as hash value of allcolumns in particular row.Every time when some field in particular row is changed we create andcall select query from our application to obtain all fields for thisrow and then re-calculate and update the hash value again.Obviously such approach is very ineffective, the alternative is tocreate trigger on update event and then execute stored procedure whichwill re-calculate and update the hash value. The problem with thisapproach is that end user could then change the date in the tables andthen run this store procedure to adjust hash value.We are looking for some solution that could speed up the hash valueupdating without allowing authorized user to do itThanks in advance,Leon
View 6 Replies View RelatedHaving no more experience than reading books online, here is an interesting idea I would like to run by you guys and you can let me know if it is feasible or tell me I need to put the crack pipe down...
We are going to increase the number of disks in our SAN, and I was speaking with the SAN administrator and he mentioned the shuffling of logical drives to match the new space. He said he is going to have to go through quite a few combinations/permutations on figuring out the best configuration for what data goes on the old vs. new to get the optimal space.
Is this something that can be modeled out? I can write something that recursively figures it out, but why not explore fun ideas with tools that may be able to do it?
Thank you in advance,
John Hennesey
Hi,
Does anyone have any idea waht this means:
aspnet_wp!resourceutilities!5!07/11/2007-10:32:00:: i INFO: Reporting Services starting SKU: Developer
aspnet_wp!resourceutilities!5!07/11/2007-10:32:00:: i INFO: Evaluation copy: 0 days left
It is take from the ReportServer log file.
Thanks in advance for any response.
Jon
I need help regarding what is a collate and where to use.If any examples with INNER JOIN it could be more helpful....
View 13 Replies View RelatedHi,
I have a table that has the ff:
LastName varchar(50)
FirstName varchar(50)
PhotoPath varchar(50)
Now I want to create a form that can accept the LastName,Firstname and also can upload a picture which in turn the filename of the image will be the value for the PhotoPath field, and eventually displays it using the repeater control.
Your Help/Info. is highly appreciated...
Jeff
Last night, I have this idea, but can not know if it can have a good performance and efficiency when the @CurrentPage is big.-------------------------------------------------------------------DATABASE:AdventureWorks DECLARE @CurrentPage intDECLARE @PageSize intDECLARE @OrderExpression nvarchar(100)DECLARE @Sql nvarchar(500)SET @CurrentPage = 1SET @PageSize = 10SET @OrderExpression = N' employeeid 'DECLARE @BigTop intDECLARE @SmallTop intSET @BigTop = @CurrentPage * @PageSizeSET @SmallTop = (@CurrentPage -1) * @PageSizeDECLARE @StartTime datetimeSET @StartTime = GETDATE()SET @Sql = N' SELECT TOP (' + CAST(@BigTop AS nvarchar(10)) + ') * FROM humanresources.Employee '+ ' EXCEPT '+ ' SELECT TOP (' + CAST(@SmallTop AS nvarchar(10)) + ') * FROM humanresources.Employee ORDER BY ' + @OrderExpressionEXEC sp_executesql @SqlDECLARE @EndTime datetimeSET @EndTime = GETDATE()SELECT DATEPART(s,@EndTime-@StartTime)SELECT DATEPART(ms,@EndTime-@StartTime)GO-----------------------------------------------------------------
View 6 Replies View RelatedI have a complex select statement that is used in several stored procedures. I decided that instead of having x number of T-SQL scripts with the same exact select statement that I would to put this query into a view and then do a select * from View. Recently an instructor told me that this was a bad idea and that anyone who uses a select * from anything should be fired. When I asked for his reasoning his response was to say the least abnoxious. I can understand why a Select * from Table might be a bad idea as the table definition can change, but the chances of a view changing seems much less likely.
Is a view a good idea in this case? Is the Select * from View really a bad idea?
Thanks
I am looking for a good reference on hardware specs for a dedicated SQL server. I don't want to talk to vendors, because I'm not looking to get snowed. Does anyone know of any resources? The server is to be a dedicated dataserver, for about 300 clients.
View 9 Replies View RelatedCan anyone tell me if turning on the AUTO_UPDATE_STATISTICS option will have any adverse effects on performance? I've read that it is possible for it to adversely affect performance, because SQL Server “takes a quick break to update database statistics in the middle of the day”. The book does not explain why and I'm always leery of simply accepting something, simply because I’ve read it from a book. This is the first time that I’ve ever heard this and cannot find anything from SQL BOL or any other source.
View 2 Replies View RelatedHi.. Never used MSSQL 2000 db. I have this website that uses mssql2000. I want to export it and convert db in mysql. Please help me understand how i can do that. I have all login info..
View 1 Replies View RelatedHi, all..
One database called POS in a server A is replicated with another server B with merge replication.
Server A is distributor and publisher.
I tried to replicate POS db it keeps giving me error following.
"SQL Server Enterprise Manager could not retrieve information about database 'POS'
Error 21776: [SQL-DMO] The name 'POS was not found in the ReplicationDatabase collection. If the name is a qualified name.
user [] to separate various parts of the name, and try again."
When other try it gives following error..
SQL Server Enterprise Manager could not enable database 'POS' for merge replication.
Error 20736: [SQL-DMO] This object has been dropped from the server.
I did all I can do..
I reinstalled sql server and tried again.. but it gives same error..
Does anyone has any idea of this problem????
Please post any idea.
Thank you...
Good morning
In messages system i have table to store messages and another table to contain the links which the message is posted through
Now i want to delete a link but i don't want to delete the message sent through this link
The problem:
When displaying the message sent what will be found in the link field (i.e. for the deleted link)
If any one has a good idea for doing that please reply to me
I don’t want the sql code
I want just the idea
Thanks
kind regards
mohammed Al Maghraby
Hi
I have about six different entities that can have zero or more note entities associated with them. The easy way to do this is obviously to have a different "note" table for each of the entities i.e. WorkItemNote, CustomerNote etc.. But I would much rather have a single "note" table since they would all be identical, so I came up with this design:
CREATE TABLE WorkItem
(
WorkItemGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()),
-- rest of table declaration removed for bravity
)
CREATE TABLE Customer
(
CustomerGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()),
-- rest of table declaration removed for bravity
)
CREATE TABLE Note
(
NoteId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ReferenceGuid uniqueidentifier NOT NULL,
Text ntext NOT NULL,
-- rest of table declaration removed for bravity
)
This way I can get notes associated with a given entity, either Customer or WorkItem, by just selecting from the Note table with its WorkItemGuid or CustomerGuid.
My question is: Is this the best approach to what I am trying to accomplish?
(ps: Apologies if "many to one" is not the right terminology)
Regards, Egil.
Good morning
In messages system i have table to store messages and another table to contain the links which the message is posted through
Now i want to delete a link but i don't want to delete the message sent through this link
The problem:
When displaying the message sent what will be fount in the link field (i.e for the deleted link)
I tried to move the deleted link data to separate table (EX: deletedLink) but if the user added new link with the same name as the deleted link? The problem mainly in when displaying the message sent i will have the same link twice one for the deleted and the other for the added one.
If any one has a good idea for doing that please reply to me
I don’t want the sql code
I want just the idea
Thanks
mohammed Al maghraby
With respect to my (now not so recent) thread on Concurrency, I would liketo run my idea past you gurus to see if its a runner. First, a brief recap:I have a single user system (one user, one copy of the software, one copy ofMSDE, one machine) that I wish to convert into a multi-user/single databasenetworked system. The problem I had was that a lot of information isfetched from the database and cached in the client program (the programimplements a tree structure, similar to a file system, and each of the nodesin the system has properties). The concurrency issue revolved around havingmultiple users updating these properties and possibly able to modify thetree structure and there being no way to notify the other clients that theyneed to refresh their data structures. Consider the system to be similar toVSS to look at (and in VSS, people can make modifications to the treestructure also!).Ok, one of the suggestions was time stamping each record. So, when one usermodifies the record, a second user can detect whether their timestamp isdifferent and thus whether or not their update is invalid (and also whetheror not the client program needs to refresh the properties of the givennode). How about instead of a timestamp I simply use a reference counter.ie. an integer that increments every time the record is modified (sameprinciple). I don't need to know when it was changed, just that the tworeference counters are different between when I fetched and when I amupdating the record.Secondly, I think I have to distinguish between a change in properties and achange in structure. For example, User A doesn't need to know about achange in properties for a node he is not currently looking at. However,that same user will want to be told about any change to the overall treestructure. So, I was thinking that any operations involving modificationsto the tree structure should set a "structure changed" flag in the database(increment a counter). After any operation is performed, the clientcompares its "changed" flag to the database value to see if it needs toreload the tree structure.Do you think this is workable?Thanks.Robin
View 2 Replies View Relatedwe are creating a database of sales agents. Basically I have a tableof about 35,000 people, a second one with 8000 offices, and a thirdtable of around 400,000 transactions done by those 35,000 people inthose 8000 offices. We get new data everyday that just updates theexisting tables with the updated rosters and transactions.I want to build a quick website where our recruiters can look up thosepeople and keep contact info and all that fun stuff but also pullnumbers on those people. Like* How Many Transactions that Sales Agent did last year* Rosters by office showing productionThe goal is to click the users name and see all the percentages,commissions and data like that which we will get by searching thattable of transactions by the agents ID.the problems I see right away are stuff like* If I were to pull a report showing all agents in a single office withtheir number of transactions next to their name, that is a HUGE query.It would have to search the 400k worth of records for each of theagents on just that one report.A suggestions I was givenI was told by a fellow programmer a better way to do this is to have anadditional table that houses stats info and have the SQL server runautomated reports everyday at say midnight where it updates that table.This table could show stuff like** number of transactions for each user** avg sales price on all transactions for each user** avg commission on transaction for each userlet me know your thoughtsthanks in advanceMonkey Girl
View 1 Replies View RelatedHi,
Today I discovered this command completely by accident and thought that ther are several places which we could use it in our apps.
Talking with a colleague, he is not to sure as it new to him too.
By using this to recover lost identity values, would this have any possible adverse effects on the table, indexes etc.
I can see potential problems when constraints are set between tables/keys. Anyone with any experience using this good and bad would be useful to hear.
Thanks
Adam
When I try to amend a stored procedure, I get Error 208: invalid object name when amending a stored procedureAny idea how I can amend the stored procedure?thanks
View 2 Replies View RelatedWhat would happen if I dropped the distribution db?
I'm having problems configuring distribution and after searching, I found someone that claims they solved the same problem by dropping the distribution db. The resolution is the last post on this thread: http://www.ureader.com/msg/11563430.aspx
Is this safe to do? There are currently no publications and the distribution server is not configured.
Thanks
I've been asked to document an application and I'm going through allthe Stored Procedures and trying to work out what they're supposed todo.Can anyone give me an idea of what the Stored ProcedurewsBookingListsGetAll below is trying to achieve? Is it incomplete? Ican't see any reason to pass in the Parameter, and what is the UNIONSELECT 0 all about?Many thanksEdwardCREATE Procedure wsBookingListsGetAll@DebtorIDvarchar(15)Asset nocount onSELECTfldBookingListIDFROMtblWsBookingListUNIONSELECT 0returnGO/* Table def */if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblWSBookingList]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblWSBookingList]GOCREATE TABLE [dbo].[tblWSBookingList] ([fldDebtorID] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,[fldBookingName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL,[fldAddressCode] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,[fldEmail] [varchar] (250) COLLATE Latin1_General_CI_AS NOT NULL ,[fldFirstName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,[fldLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,[fldBookingListID] [int] IDENTITY (1, 1) NOT NULL ,[fldInvoiceNumber] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,[fldPayeeID] [char] (15) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GO
View 1 Replies View RelatedWhenever something is inserted to a given table, I want to run someshell commands using xp_cmdshell. Would it be a bad idea to put thisxp_cmdshell in the INSERT trigger of this table?I understand that when using xp_cmdshell, the sql thread in questionwaits until xp_cmdshell finishes what it's doing. Does this mean ifmy xp_cmdshell call takes 30 seconds, that nobody else can insert tothis table until my xp_cmdshell and rest of the insert trigger finishup?The alternative solution seems to be frequent polling of the table inquestion; while this isn't really a great solution it would seem tocircumvent a table lock brought about by the INSERT trigger.Thoughts?Joel
View 4 Replies View RelatedOK can someone look at this idea and tell me if it sounds like it would be work and be a good way to do things
On our Virtual Server machine I have 4 Server's setup. All Running Windows 2003 Standard.
Server1 - SQL Server 2005 Standard
Server2 - SQL Server 2005 Standard
Server3 - SQL Server 2005 Express
Server4 - SQL Server 2005 Express
Now I would have Server1 & Server2 in a DB Mirror Mode. With Server3 being the witness. When Server1 Fails Server3 would tell Server2 to become primary. Then when Server1 comes back it gets updates from Server2 and vice versa if Server2 fails.
Now Server4 I was hoping to remove some of the processing that is done (currently via DTS in SQL 2000). This scheduled jobs will get the updates from another system process them and then push the changes to either Server1 or Server2 depending on who is the primary at the time.
Does this sound like it would work? Is this a solid idea in making the DB's available witha 99.9% uptime?
Hi All,
I would like to say thank you in advance for all your ideas. Here is my case i want to implement slowly changing dimension, i know that i can use SCD component of the SSIS, but because of performance issue i am thinking to use something else that can subtitute the SCD component, i want some idea from you guys if anyone has implemented before without Slowly Changing Dimension component.
If not, do you have any comment/suggestion to use the SCD component i mean if the worest comes and i use it, what draw backs does it has, for example interms of data size, performance. Note that i use Dedicated Server for the ETL in Production.
Thank you
SamiDC