if possible i want a flexibility wherein i should b able to give the gp_code and all the items in tht gp_code should b displayed in above format where in the starting monthh will b the current month .
We are using Ms SQLSERVER 6.5 on NT 4.0 service pack 3 I have joined in as a new DBA and don't know much about MS-SQLSERVER & it's replication. What I have observed out here is that one of the databases was being replicated betnn Tokyo and Kawasaki and suddenly after 22nd Feb stop replicatiing without giving any errors. I have checked the max job_id in MSsubscriber_status on distribution database as well as max_job_id in MSlast_job_info on subscription db.They seem to match.I have also tried to drop and recreate the unique clustered index on the subscribing database.But nothing seems to work.
Hi Every body, I have newly joined this group. I am new to DB administration. I wanted some information as to if my Server crashes (which has) & i reinstall SQL server, will restoring master database restore all my permissions & security which was set before crash. It would be great in anybody can help me on this. Regards, Krishna
Just a query, I have several tables that have bigint identity columns, I need to know what will happen when it reaches the upper limit. It will be most likely that earlier id fields have been deleted by the time it reaches the upper limit, so will it reuse numbers?
i have installed mssql2k in win2000 server. since it was for my personal use, i did not give a password for the 'administrator' user in win2k. later on i cahnged the admin password,but my sqlserver did not start due to login failure.
Q--Is there any feature of sqlserver-2000 by which we can restore the database with minimum time by using the parallel processor? if the answer is yes wht is the statistics with example
For example
Time reqd for restoring 20GB of database 25minutes.
with configuration of Xeon processor,1GB memory and no users are using the server.
Basic purpose of my question is i need to give a solution to the client by using which he will be able to restore 20GB of data in 1 minutes .Is there any 3rd party utility which is available in market or can we achive this in mssqlserver2000, itself by increasing the resources like memory,cpu etc.
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
I am looking for a query to solve this. I had two table's.
1) Employee Table
Employee_ID Designation ---------- ------------- Savin Database Administrator Ray Software Engineer Adam Software Engineer Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet ----------- --------- Savin Oracle8i Savin SQL Server 7.0. Savin SQL2000 Ray VB 6.0 Ray Java2.0 Ray C++ Adam Share point Adam VB 6.0 Adam Java2.0 Scott ASP Scott VB 6.0
Output I requried. -----------------
Employee_ID Designation SkillSet ------------ ------------ -------- Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000 Ray Software Engineer VB 6.0, Java2.0, C++ Adam Software Engineer Share point, VB 6.0, Java2.0 Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
There are 2 tables: 1. ItemMaster Table: This table contains all the available Items (around 500,000). Fields are: (a) ItemId Integer (PK) (b) ItemDetails Varchar(100)
2. ItemImage Table: This item contains the location of picture of few items out of above table (around 10,000). Fields are: (a) ItemId Integer (PK) (b) ImageURL Varchar(50)
Requirement: I want to create a query or stored procedure, which would return me: ItemId, ItemDetails and a flag to indicate if image is available for that item or not.
Can anybody help me out in getting this flag which has to be build dynamically in the query as its not there in the tables. And what would be the query for acheiving the desired result.
Hi, I am a new SQL programmer. I need your help for writing a query. I am able to get the desired result set by looping through but it takes lots of time and resourses to run and crashes my machine. Is there any way to write a sql statement which performs batchwise? I really appreciate your help.
Following is a data from a view called ind_results.
last_name first_name course_code course_title score ------------------------------------------------------------------------- Walker Jay 1 Mid-Term Exam 71 Walker Jay 2 Final Part I Online Exam 64 Walker Jay 3 Final Exam Part II Online Exam NULL
Reed Steve 1 Mid-Term Exam 70 Reed Steve 2 Final Part I Online Exam 78 Reed Steve 3 Final Exam Part II Online Exam 80
I need help in writing a query, where
1) If Score is null for either course_code 2 or 3, display score = 'incomplete' for course_code 2 and 3 (for Jay Walker) 2) If Score is not null for either course_code 2 or 3, display score = average value of score for course_code 2 and 3 (For steve Reed)
i.e. the resultset should look like this.
last_name first_name course_code course_title score ------------------------------------------------------------------------- Walker Jay 1 Mid-Term Exam '71' Walker Jay 2 Final Part I Online Exam 'incomplete' Walker Jay 3 Final Part II Online Exam 'incomplete'
Reed Steve 1 Mid-Term Exam '70' Reed Steve 2 Final Part I Online Exam '79' Reed Steve 3 Final Exam Part II Online Exam '79'
I am trying to write a query where I get the following result. Basically I have to display latest part_number in the chain with status as 'current' for any part number that is superceded by another part_number.
I have written a code using cursor. But I just learnt that cursor should not be used. So I am looking for different syntax so that I can learn programming without cursor.
Hello I have the following query that is producing the following errors;
Code: declare @temp_request as varchar(8)
Select request as temp_request,BUSINESS_REQ_ID,TEST_CASE_SOURCE as SOURCE from TEAM3B_PULL_TOTAL_TST if (not exist (select * from TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST where REQUEST = temp_request)) begin
Insert into TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST(request,bu siness_req_id,source) select request,business_req_id,test_case_source from TEAM3B_PULL_TOTAL_TST where request=temp_request end
Else
begin Insert into TEST_REPORT.dbo.RDD_UPDATE_TST(request,business_re q_id,test_case_id,test_case_descr) select request,business_req_id,test_case_id,test_case_des cr from TEAM3B_PULL_TOTAL_TST where request=temp_request end
Errors:
Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'select'. Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near ')'. Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'Else'.
I am looking for a query to solve this. I had two table's.
1) Employee Table
Employee_ID Designation ---------- ------------- Savin Database Administrator Ray Software Engineer Adam Software Engineer Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet ----------- --------- Savin Oracle8i Savin SQL Server 7.0. Savin SQL2000 Ray VB 6.0 Ray Java2.0 Ray C++ Adam Share point Adam VB 6.0 Adam Java2.0 Scott ASP Scott VB 6.0
Output I requried. -----------------
Employee_ID Designation SkillSet ------------ ------------ -------- Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000 Ray Software Engineer VB 6.0, Java2.0, C++ Adam Software Engineer Share point, VB 6.0, Java2.0 Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
I am looking for a query to solve this. I had two table's.
1) Employee Table
Employee_ID Designation ---------- ------------- Savin Database Administrator Ray Software Engineer Adam Software Engineer Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet ----------- --------- Savin Oracle8i Savin SQL Server 7.0. Savin SQL2000 Ray VB 6.0 Ray Java2.0 Ray C++ Adam Share point Adam VB 6.0 Adam Java2.0 Scott ASP Scott VB 6.0
Output I requried. -----------------
Employee_ID Designation SkillSet ------------ ------------ -------- Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000 Ray Software Engineer VB 6.0, Java2.0, C++ Adam Software Engineer Share point, VB 6.0, Java2.0 Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
I have a report which has a table and that table has 4 columns
I want to represent the Data like this.
Company Match or Profit Sharing or Safe Harbor Company Match or ProfitSharing or Safeharbor
Years of Service Vesting Years of service Vesting
1 40 1 50
I have 3 text boxes saying Company Match, Safe harbor and Profit Sharing, and the User normally can click 2 checkboxes
Suppose if the user clicks only company Match, i want the company Match to display on the left hand side if the users clicks on 2 things say company match and safe harbor.
I want the Company match to come on the left and safe harbor to be on the right. and my Expression is as follows:
WITH SET [FilteredAccountSet] AS 'order( [Acc€”BU_ Account_BCDsc].[Account],[Measures].[_MeasureBillingCode],ASC)' SET [FilteredBUList] AS 'order( [Acc€”BU_ Account_BCDsc].[Business Unit],[Measures].[_MeasureBillingCode],ASC)' SET [FilteredBillingCodeSet] AS 'order( [Acc€”BU_ Account_BCDsc].[Billing Code Description],[Measures].[_MeasureBillingCode],ASC)'
SET [FilteredEngagementGroupSet] AS '[Acc€”BCEngTypeGroup_BCDsc].[Billing Code Engagement Type Group]'
MEMBER [Measures].[AverageTeamSize] AS '[Measures].[Consultant Utilization]/(COUNT(GENERATE({[Date€”Year_Month_Day]. [Date€”Year_Quarter_Month].[All Date€”Year_Quarter_Month].[2007].[Quarter 1].[May]: [Date€”Year_Month_Day].[Date€”Year_Quarter_Month].[All Date€”Year_Quarter_Month].[2007].[Quarter 1].[July]}, DESCENDANTS([Date€”Year_Month_Day].CURRENTMEMBER,[Date€”Year_Month_Day].[Day]))))'
OPEN orabaan declare @batch int, @ttype char(3), @doc int, @ttdr float, @ttcr float, @diff float, @s1r int, @s2r int
WHILE @@FETCH_STATUS = 0
BEGIN FETCH NEXT FROM orabaan
INTO @batch, @ttype, @doc, @ttdr, @ttcr, @diff
if @diff < 0 update ttfgld102777 set t_amth_2 = (t_amth_2 + @diff) where exists ( select top 1 t_amth_2 from orabaan where t_dbcr = 1 ==> and t_btno = @batch and t_ttyp = @ttype and t_docn = @doc ) ==============================
I want to add a clause to auto update t_btno when value occur as "t_btno" is variable int
Hello. I have a question. I have a table that keeps track of claims being made by clients (like insurance claims). There is a table which has 2 fields. Field1 and Field2. Field1 has the original claim number. If an adjustment is made to the claim, a new claim number will be generated. For example if a person pays $100 and files a claim, he is issued a claim number which goes into Field1. If that person spends some more money towards the same issue and then files another claim then he is given a new claim number which is stored in Field2.
Field1--------------Field2 -------------------------------- 100--------------------150 150--------------------200 200--------------------250 250--------------------Null (No more adjustments made) and so on.
So in the query I will supply a claim number (say 100). I need to get the history of all the claims attached to it. In this example 100, 150, 200, 250.
Also if the claim number provided is 200 I need to get the original claim number (which is 100) meaning I need to back track and forward track too. I need this in SQL Server 2000.
i am working on asp.net i am passing two values through querystring to the next page, that values are comming to next page but i want to display results on these two crieria hats. i have written following query which working fine in sql server 2000 SQL 2000==> select caterername, ccity from caterer where ccity='matunga' and cateringfor like '%event%' but when i am trying to do this in asp.net its not working. following code accepting only one value . i want to compair two value. Please guide me how to write above query in asp.net using 'and' and 'like' operatorasp.net ==>select * from addahall where HCity='" + city + "' ==> city is variable please let me know as early as possible i have very less time.
hi frenz: This is my query:it shows the following error:Incorrect syntax near the keyword 'as'.plz let me know my error, and how to correct it.
My Query:
SELECT EH.EmpID, EL.UserName,EL.Password, E.FirstName, E.LastName, E.DesignationID, E.DepartmentID, E.Sex, E.DOB, E.Age, E.Address, E.Email, 'ContactNo'= Case When E.phone is not null Then E.phone When E.phone is null Then E.Mobile End ,E.DateOfjoining, D.Designation, DE.Department,E.HobbyID where E.HobbyID in (select EH.HobbyID from EMP_Tbl_EmployeeHobbies EH inner join EMP_Tbl_Hobby H on EH.HobbyID=H.HobbyID) as item1, From dbo.EMP_Tbl_Employee E Inner Join dbo.EMP_Tbl_Designation D On E.DesignationID=D.DesignationID Inner Join dbo.EMP_Tbl_Department DE On E.DepartmentID=DE.DepartmentID Inner Join dbo.EMP_Tbl_EmployeeHobbies EH On E.EmpID=EH.EmpID Inner Join dbo.EMP_Tbl_EmployeeLogin EL On E.Sno=EL.Sno
The question is regarding licences for clients who want to put their databases on the internet using our web servers. We have this in our price list as a service. We have been offering this service for a ong time.
Now we are using old licences v 7.0 for NT servers, and we need version w2000 for w2000 servers.
Our doubt is how to work the concurrent users through the internet. Can a server with mnimum number of licences serve hundereds of users through the internet,since users don't access the sql databases directly?
If anyone could help I would really appreciate it, or even offer a reference article
Hi all, I am running a query which was created by front end application on (9 tables and one view) VIEW was created on 3 tables...Each table is having millions of records.. In this query there nearly 30 where conditions..are there.. When i run query plan , it is using HASH JOIN.. when i run a query, it is giving ERROR 1101..It is occupying all tempdb size, i gave autogrowth of tempdb, then also tempdb memory is not enough for this query... I am having all possible indexes on all tables including tables which are used in VIEW. and also i tried to run PERFORM INDEX ANALYSIS on this query , it is running ,after 10 minutes i stopped the PERFORM INDEX ANALYSIS.. because it is taking so much of time.. I believe this query is retrieving millions of records,but i dont know what to do with this one.. Pls anyone suggest me, how my query is taking so much time , is this because of HASH JOIN. I think if we have service pack2, can avoid this problem, am i right(if we have so many records in table)
id1 is an identity column. I have to populate third column which is id3 based on the data in id2.
I have to group similar values in id2 together and insert 1,2,3 accordingly in id3. For eg: we have three 100's in id2, so we should have values 1,2,3 for id3. we have two 45's in id2, we should have values 1,2 for id3.
Hi Guys, I have a table created like (cname,perfweek,orderstaken,callsmade). the application give a report of perticular salespersons(cname) performance by ordertaken and callsmade. somedays back, they have entered different cname for the same person. What I wanted to do is, when they give me correct cname and the wrong cname, I will have to findout in a perticular week, is there are duplication of cname and if then I have to add orderstaken and callsmade to the correct Cname for that particular week and after that delete the wrong Cname detail for that particular week(because it is added to the correct cname rows for that week). And then I have to change the wrong Cname to the correct Cname for all the rows, if there is no data found for right cname matching for that week. cname perfweek orderstaken callsmade ----- ------- --------- -------- abc 1 3 4 bbb 1 5 6 abc 3 3 1 bbb 3 2 3 bbb 2 4 5
in this eg: abc is the right cname and bbb is the wrong cname. Here what I have to do is I have to combine rows for the correct and wrong cname for that particular week ie: now the table looks like : cname perfweek orderstaken callsmade ---- ------- ---------- ---------- abc 1 8 10 bbb 1 5 6 abc 3 5 4 bbb 3 2 3 bbb 2 4 5 (note: after combine, I have to delete the bbb for the perfweek of 1 and 3) and then I have to update the rest of the wrong cname to the correct cname. Then the firnal table looks like
I am new to SQL Server. I want to know about SQL Query execution and compilation sequence.
Consider the following case:
SELECT A.COL1, B. COL2 FROM TAB1 A INNER JOIN TAB2 B ON A.COL5 = B.COL5 WHERE A.COL3 = 'A' AND B.COL3 = 'B'
Now as per my records into TAB1 and TAB2, condition B.COL3 = 'B' can reduse more number of rows then condition A.COL3 = 'A'. So, please let me know above way to write the query is proper or not in given senario.
It would be great if one can provide the details how Sql Server 2000 compile and execute the query and some relavent data to optimize the performance while writting SQL query.
i want to create a join query (for view) that will show one data per day for each agent. just a select query joing these two tables..Seems like the date field is given me problem i want result like below
SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue FROM dbo.HOLDING INNER JOIN dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num Group by dbo.HOLDING.Account_ID
Account_ID is the same in both the queries ie in both the tables Holding and Account.
I need the output like this
Select Account_Id, Branch_Cd, MarketValue from -------
But MarketValue should be calculated exactly in the above method. How do I combine these two queries. I need it asap. Help me out.
I am facing a query problem.i have 2 tables called emp-table and report-table.report table for listing report of employees. following are criterias..
1 I want list all reports from report-table 2 But no report should have empstatus 5 3 reports need not necessary to have emp-table reference.ie emp-id reference in emp_table. briefly saying i want all reports from report table,in which no one have empstatus 5
