On what basis, SQL server decides the sequence of these rows?
I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.
I wrote some views in sql2000 and all was well. I upgraded to sql 2005 and non of the order by clauses work when I run the views directly. If I open the view up in desing mode and run them, they work. Any Ideas? Thanks, Rick
I have a fact table with SalesAmount. I have a dimension with SalesType - either counter or direct.
I want to display a report that shows direct sales by region. No problem. I group on region and filter by direct sales.
However, I also want to show direct sales as a percentage of total sales. I've played around with multiple tables but I can't seem to find a way to display both direct sales and total sales in the same row of a report.
Recently I was stumped on a problem where I was granting permissions to a user, from within a script that was creating a stored procedure. Then I would check the permissions on the procedure, but the permissions were empty. It turned out that I was missing a "go" statement to seperate the create procedure statement from the grant statement. So that got me to thinking about what other kids of statements must be seperated into seperate batches ? I would thik that anything that is being created must be seperated from any statements that grant or alter permissions, because the items would need to exist first.
Hi, I want to ask a basic question, that is IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN????
Somewhere in the MSDN library I read the following line: "A clustered index physically sorts the table's contents in the order of the specified index columns"
But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC) So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC or If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.
Here is my Query, I don't know whether I'm getting it right?
--Quarter 1 SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail INTO#Quarter1 FROMdbo.tblRPT_Spend AS A INNER JOIN dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .
below is the 2 sp .
--First SP ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor] AS BEGIN SET NOCOUNT ON DECLARE @Source_RowCount int DECLARE @New_RowCount int
I want the count of orders of a particular table on weekly basis i.e if date given to me is 10/3/2014 then my output should be count of orders from date 10/3/2014 to 09/3/2014(one week) then count of orders from 2/3/2014 to 08/3/2014(another week) and then from 24/2/2014 to 01/3/2014(another week).....
I can fetch the counts for total present and absent
Query i have tried is
Declare @StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458' Begin select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
How do I format a date value in SQL 2000 sp? Tired the following none are returning the quivilent rates. What is the syntax please? SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03/21/2008') SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '2008/03/2008') SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03-21-2008')
I have to import data into 2 tables on a daily basis. The data is provided as a flatfile. In order to fullfill this task the tables have to be truncated first. Are there any possibilities to do this job automatically with dts, or do I have to write an Interface in VB or something like that?
hello guys ! i need a little help in an update statement.
i have two tables as
table1(id1,name) table2(id2,date,val2)
what i want is to set val2=45 in table2 where date=4/4/2007 (say) and id2, now id is to be obtained from table1 based on the name;
like table1 --------- (1,"akmal") (2,"peter") (3,"kamal")
I know the name as peter, but i want to used its id1 (2) in the update statement. You might have got the idea that id2 is a foreign key referring to id1.
Hi All I am using following code for my application SELECT convert(varchar(10),acr._eventTime,120) [Date], sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning], isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK], FROM ActiveCheckIssueResult r GROUP BY convert(varchar(10),acr._eventTime,120)
it gives me Result as,
Date warning OK --------- --------- ----- 1/1/1008 1 1 2/1/2008 0 2
i want count on weekly basis Could any one help me?
I want to set my job agent to schedule for second basis but in EM i found by right clicking job in EDIT Recuring Job Schedule i m getting option in Hr and MM but not in SS so where i can set this in seconds (SS) or through QA????
Currently database1 and database2 are planned to be synchronized with the interval of 5 seconds!! ( dont know exact, but i select run continoulsly on subscriber screen ) This could cause serious performance issues as both applications will be live on difference servers.
how can i Synchronization should be performed on day basis or it should be modification basis to avoid any performance issue on live application
i am using merge replication Local distributor and subscriber on another server .
I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.
I mean, are there big differences between store data as XML format and nvarchar?
On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far. SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3], [column4], [column5] FROM [table] WHERE [column4] = 'condition4' AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate ", Conn) ; cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text)); cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text)); cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text)); I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help.
I have recently upgraded one of our servers using the in place upgrade method to SQL Server 2005 from SQL Server 2000.
We are currently having an issue with stored procedures as they appear to be running slowly and the only way to correct this issue is to recompile them on a daily basis.
Has any one else had a similar issue, any ideas what could be causing this to occur.
I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.
The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"
I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).
The code I use to capture errors and trace information follows: UPDATE PortfolioPerformance SET PrevDate = @PrevDate, DailyPerChg = GPP.DailyPerChg, DailySumPriceChg = GPP.DailySumPriceChg, SumCurrPrice = GPP.SumCurrPrice, SumPrevPrice = GPP.SumPrevPrice, StockCount = GPP.StockCnt, AvgHighPriceRatio = GPP.AvgHighPriceRatio, AvgLowPriceRatio = GPP.AvgLowPriceRatio, Volume = GPP.Volume FROMPortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP WHERE PP.PortfolioID = GPP.PortfolioID AND (PP.CreateDate = GPP.CreateDate AND PP.CreateDate = @CreateDate) AND PP.PrevDate IS NULL
NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.
So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?
How would you solve this problem? Would you use replication? SSIS? Schedule stored procs? A third party data tool?
We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.
We need only the new INSERTS or UPDATES for the day to be copied to head office. The copying process should happen after business hours for that office.
Some cleaning of the data needs to happen before updating head office.
Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.
We want something robust, performant and easy to troubleshoot.
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
Anyone have any experience in connecting to a Basis database? I want to build an SSIS package to ETL data out of a Basis database and into a sql server 2005 db. I have downloaded the ODBC driver, but am not usre how to plug it in to SSIS to do teh extraction.
Also I am totally new to Basis...it is not an RDBMS at all but the literature says it supports SQL queries, so I am not sure about where to start.
I've just spent the most frustrating 4 hours trying to install SP2.
I kept getting "unable to start SQL Server service....see BOL ...re starting the service manually" messages; with the only options being to Retry or Cancel. Retry resulted in the same message. Cancel resulted in all components except SQL (sqlexpress) being installed - therefore totally useless
No mater what I did i could start the SQLservice as it did'nt show in the list of Services; nor did the SQL EE programme show in the Add/Remove Programs...evenn though the other components did.
I deleted/removed everything that was istalled; cleaned up the Registry; and tried again...
..and again
..and again
FIVE install / re-install failures; numerous registry cleanups!!!!
I was at the point where it looked like i'd have to reformat my hard disk, and completely re-install XP, all aplications i.e. Office 2003 etc in order for the thing to work.
I gave it one more shot, but this time, during install
- selected all components except SDK
- I unchecked the Start SQL / BROWSER at startup
- Checked the Enable User Instances and Add to Administrators boxes
Lo and behold....it installed!!!
I'm still very worried as the registry is full of "duplicate" entries i.e. Blah blah blah and blah blah blah.1
But at least the thing is installed.
Would interested to hear if anyone has had similar or other problems with the SP2 install/upgrade
----------------------
For info:
I ran the install as Administrator
IIS not installed
Win XP Sp2 fully updated
Dell Inspiron XPS M1710 laptop with 2gb Ram; 2ghz Duo core processors
I would like to force only certain users on certain databases to use encrypted connections for accessing our 2005 SQL Server. I've found a clunky way to do it, via registering a login event notification and using the kill statement. However, that's ugly, and I don't think there is any guarantee that the event will always fire before an evil client could get off a few evil commands. Is this supported, or is the only supported feature the forceencryption option, which is server-wide?
I'm creating 15 reports based on one data source. Once I created, I'm supposed to run this on daily and monthly basis. I'm going to use snapshot option in Report Manager. Is it fine? Do I need to create a data base too?
Also when I change the target URL address in the report property box, can I deploy it to any other server? Do I have to change the settings of report configuration manager too, inorder to publish reports on another server(not in my local machine)
Hello All,i have 2 excel files which i want to upload to sql database. i know how to upload to the folder. my question how to i use these excel files and convert them into tables in sql database.i have to do this on a regular basis. is there anyway i can do this. the 2 files when converted to tables, i need to create relationships etc. any suggestions or references please. i appreciate it. Thanks
