Why Minor Diff Makes Big Lag

Mar 31, 2008

The DB Enginee is SQL2005 SP1. In MSSMS

the following query always finishes within 1 minute

SELECT *
FROM t1 INNER JOIN t2 ON t1.key= t2.key
where t1.StartTime >= '3/19/2008' AND t1.EndTime <= '3/20/2008'
AND t2.StartTime >= t1.StartTime AND t2.StartTime < t1.EndTime

however, the following always takes 10+ minutes
declare @pStartDate datetime
declare @pEndDate datetime
set @pStartDate='3/19/2008'
set @pEndDate='3/20/2008'
SELECT *
FROM t1 INNER JOIN t2 ON t1.key= t2.key
where t1.StartTime >= @pStartDate AND t1.EndTime <= @pEndDate
AND t2.StartTime >= t1.StartTime AND t2.StartTime < t1.EndTime


Both return same result. The only diff. is the blue part. Why the second one performances so badly?
Thanks.

View 4 Replies


ADVERTISEMENT

Select Data From Diff. Tables On Diff. Servers

Feb 12, 1999

I need to write a 'select' statement to fetch data from different tables, which are located on different servers.
Can any one help in writing this 'select' statement with out moving the tables on to same server.


Thanks in Advance.

Murali Raparla.

View 2 Replies View Related

|DataDirectory| - Minor Issue SQLev

Oct 16, 2006

It seems that the |DataDirectory| macro is giving VS 2005 fits. It's a minor issue though and I've stumbled on it recently.

The issue actually doesn't come up until you try to "add a new data source" to your project (after you've made the modification in your app settings/Project Properties to use the macro). So if you started of with ".something.sdf" and then changed it to use the macro, things do run fine....until you try to add a new datasource (new database datasource)

The error is: "Illegal characters in path" which is the pipe symbol.....

While on this subject, here's a tip to those who maybe going through some debugging mysteries:

Even if your sdf file is in your app "root" folder, if you debug, the file your app is actually working on is in the "indebug" folder. So it pays to create another connection in your Server Explorer to this file while debugging.

I spent quite some time trying to figure out why my code didn't seem to be working and got even more frustrated since obviously no errors were reported. Turns out I was looking at the wrong db file....hope this helps...

View 3 Replies View Related

Renaming A Project... Some Minor Stuff

Apr 16, 2008

I have a project. Originally when I created this project, I called it "Project_A", (and I did not create a solution directory).

Doing this created a folder called "Project_A" with the following files within it. I guess these are meta data files and I shouldnt be messing around with them. Anyway, I see

(1) Package.dtsx (The default package that gets created with a new project)
(2) Project_A.database
(3) Project_A.dtproj
(4) Project_A.dtproj.user
(5) Project_A.sln
(6) Project_A.suo

When I rename my project in the Solution Explorer to "Project_B", the following happens in my folder.

Everything remains the same except Project_A.dtproj gets renamed to Project_B.dtproj and a new file called Project_B.dtproj.user gets created.

Now, my questions.

Question 1: What if I wanted my folder to have the same name as my project? Can I just change the folder name in Windows Explorer without having any ill-behaviors later on? I always have 1 project to 1 solution so I want to use the convention of keeping my folder named the same as my project.

Question 2: What about the files that are still named with Project_A dot blah blah. Is it bad if I renamed these to Project_B dot blah blah?



View 7 Replies View Related

Copy And Paste Minor Irritation

Sep 24, 2007

Can anyone explain why when I copy a data element and attempt to paste it it always appears waaay off screen in my SSIS 2005 package? Why won't it paste where I am right-clicking and telling it to go? I hate having to hunt for the element in the flow.

View 1 Replies View Related

I Need To Make A Minor Change To A SQL Server Script That Is In Production. Need Advice So I Don't Screw Something Up.

Mar 1, 2007

Hello. I am not very strong with SQL server. But I know enough to get my way around. The reason I am asking forhelp is that I need to change a SQL server script that resides on one of the production database servers of thecompany I work for. I just need to add two new lines to the stored procedure.(See the lines that are bolded.) Thesevalues are [MO_FAX],[MO_EMAIL].
Can someone tell me if there is a best way of going about doing this? Can I just simply open up SQL server and quickly make the change? The big issue here is that this script is used in production. So I am just a little worriedabout screwing something up. Anyway, I would appreciate some good advice on this?
 
 
/* Returns all data given the region and country */ALTER  PROCEDURE [dbo].[GetAllInfomration]
AS SELECT   [COUNTRY],  [Company_NAME],   [CompanyAddress],   [CompanyPhone],
  [MO_FAX]  [MO_EMAIL]
  [CompanyFax],
 FROM [InfoLookup].[dbo].[Company_Contact]  RETURN

View 5 Replies View Related

How To Makes Sum In Sql

May 5, 2006

I want to get different sums from an item base. I have taken the number of every order (ord) from an order base and goes to the item-base like this:

Set Rss = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT array(sum(numitems)) from oitems where orderid= " & ord
Rss.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

I have not tried to run the sql- the array is just a dummy to show the idea.

Actually I want to make an array of the sum of items with the same numitems (i.e. the same productnumber)

if the sql runs I will write the array like this:

While Not rss.eof
response.write array(sum(numitems))
'or whatever way I have to write it
rss.Movenext
Wend

...anyone who has a good idea of how to do this?

View 20 Replies View Related

GO Makes Things Hugley Faster

Oct 15, 2007

I have a data migration script that takes about an hour half to complete. The script conists of several insert statements that pull data from another sqlserver database while doing a fair amount of manipulation.

If I put a "go" in after every insert statement. The job finishes in 30Seconds. can some explain this huge performance change.

View 1 Replies View Related

Sorting Makes Table Invisible

Sep 13, 2006

I have a table which visibility can be toggled by a text box. By default it is invisible. After it is made visible, clicking a sortable column header makes the table invisible. Does this mean sorting makes the table go back to its default visibility?



View 3 Replies View Related

Add/Remove DataViewers Makes VS To Crash

Sep 14, 2006

Hi all,

I'm sick of VS crashing when I add/remove DataViewers any idea why this could happen ?

Sébastien.

View 4 Replies View Related

Express 2005: How To Makes Changes Without Stopping The Web Site?

Jan 24, 2008

 Are there any way I can make changes to my database without stopping the web site? I'm using SQL Server 2005 Express for my web shop. And each time I have to add or change a table or Stored Procedure I have to stop the site. What I do is to stop the web site (in IIS), restart the IIS and then open the database in the SQL Server 2005 Express Management tool. If I don't "release" the file first I get an error when I try to open the database file in the Management tool.But there must be a better way to do things? I also would like to take bacup of the database without stopping the site, but how? There must be others that have had the same problems? Regards, Sigurd 

View 3 Replies View Related

In Outter Joins, What Makes A Table Either Right Or Left?

Dec 16, 2002

Until today, I was always under the impression that left vs. right was determined by which side of the comparison operator the table was located.

In other words:
LEFT JOIN LeftTable.ID = RightTable.ID

would pull all the records from LeftTable and those that matched from from RightTable and that:

RIGHT JOIN RightTable.ID = LeftTable.ID

would pull exactly the same result set but I was wrong. So, if it is not the table position in relation to the comparison operator, is it simply that the tables listed first in the FROM clause aren the ones "Left" of those subsequently entered?

View 2 Replies View Related

Mirror Busy Makes Principle Slower?

Jun 6, 2008

Hi everyone,

A relatively simple question but I have no been able to find an answer.

We have a SQL Server mirror Active/passive with High availability. So the usual Witness with failover.

However, we are using the Mirror server for reporting and letting the users query without limit. We sometimes have problems (slowness) with the Principle server and I'm wondering the following.

If the mirror server in high availability mode is maxed out will this affect the principle server because it takes longer to commit the data on both servers?

It seems obvious but I would like a clear response.

Thanks.

View 5 Replies View Related

Irritating ADODB Error... Makes No Sense To Me

Aug 22, 2007

Hi Guys,

I've been programming with SQL 7 for about a year and my company has finally decided to go SQL 2k5.

I've come accross a really irritating error when writing to the DB via ADO in ASP pages. I have a column in a table that is auto-incremental.

In SQL server 7 you just make an ADODB.Command object and enter the SQL query 'insert into table (columns) values ('val...') now for SQL 7 I can completely leave out the auto-incremental column (called 'ErrorNo') and simply specify the other columns and values in my insert query.
e.g. where my table is called master_error:

ErrorNo int identity (1, 1) not null
,ReportedBy char(10)
,ExpectedFixDate datetime


With ErrorNo being an auto-incremental identity, my query would be

INSERT INTO master_error (ReportedBy, ExpectedFixDate) VALUES ('Ben','01 Sep 2007')

this works perfectly with ADODB.Command when writing to SQL Server 7 from IIS 5.0

however when I execute the exact same command on the exact same table using ADODB.Command writing to SQL Server 2005 from IIS 6.0 I get an 'error 500 internal server error'

IIS log states:
|166|80040e14|[Microsoft][ODBC_SQL_Server_Driver][SQL_Server]Cannot_insert_the_value_NULL_into_column_'ErrorNo'__table_'ReACToR_V1.0.dbo.master_error';_column_does_not_allow_nulls._INSERT_fails. 80 - 192.168.78.159 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30) 500 0 0

I thought perhaps SQL 2005 might have different syntax so I typed the query directly into SQL Server 2005's version of query analyser and guess what... it worked fine.

I can't tell where the error lies. I find it hard to beleive that the error is in the code of my ASP page as it works perfectly against a sql 7 db.

Any ideas?

Many Thanks,
Ben Ward

View 8 Replies View Related

Sort Transformation Makes A Lot Of Temp Files

May 29, 2007

Hi all,



I have a problem with a Sort Transformation, I have a CSV file with 200'000 rows the csv file is about 30Mb. When the rows are processed in the Sort Transformation, SSIS generates around 160 temp files of about 10Mb each.



How can avoid so many temp files to be generated ?

View 4 Replies View Related

ASPNETDB Makes FULL TEXT SEARCH Impossible?

May 13, 2008

Hi There,
I have a database which I attached to Sql Server 2005 Express (With advanced features) and enabled full text search.  I created a FT catalog and FT index on a column and can successfully run the following query:SELECT expertise
FROM faculty_user
WHERE FREETEXT (expertise, 'Berry' )My issue is that full text search requires user instances to be turned off. Simply setting "User Instance=False" over in my web.config's connection string (in VWD) does not solve the problem.I still get the following error and I think it may be caused by my ASPNETDB. I can't find my connection string to ASPNETDB to turn off user instances and I can't diagnose my other connection strings because of it.Generating user instances in SQL Server is disabled

Here is a sample of my other connection strings:
<add name="myConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabase.mdf;Integrated Security=True;User Instance=False" providerName="System.Data.SqlClient"/>
From what I understand, I should also not be attaching the database from my connection string like this because it relys on User Instances? I am officially confused! Thanks for reading, -Derek

View 1 Replies View Related

Multiple Table Joins Makes Query Go To Sleep

Jul 23, 2005

Hello All & Thanks in advance for your help!Background:1) tblT_Documents is the primary parent transaction table that has 10fields and about 250,000 rows2) There are 9 child tables with each having 3 fields each, their ownPK; the FK back to the parent table; and the unique data for thattable. There is a one to many relation between the parent and each ofthe 9 child rows. Each child table has between 100,000 and 300,000rows.3) There are indexes on every field of the child tables (though Idon't believe that they are helping in this situation)4) The client needs to be presented a view that has 5 of the mainfields from the parent table, along with any and all correspondingdata from the child tables.5) The client will select this view by doing some pattern-matchingsearch on one of the child records' detail (e.g. field-name LIKE%search-item% - so much for the indexes...)Problem:When I do the simple join of just the parent with one of the children,the search works *fairly* well and returns the five parent fields andthe corresponding matching child field.However, as soon as I add any one of the other child records to simplydisplay it's unique data along with the previously obtained results,the resulting query hangs.Is the overall structure of the tables not conducive to this kind ofquery? Is this a situation where de-normalization will be required toobtain the desired results? Or, more hopefully, am I just an idiotand there is some simpler solution to this problem?!Thanks again for your assistance!- Ed

View 9 Replies View Related

Transaction Log Truncate Makes You Loose Current Information?

Jun 28, 2007

Hi,



I have one database configured with the Recovery Model "Simple".

I am getting alot of full transaction log messages... is this supposed to happen?



Another question is:



Imagine i am in a middle of a big select into statement... and in another query i run the backup truncate log... am i going to loose information on the other batch ("select into?")??



Kind Regards,

Luis Simões

View 5 Replies View Related

Adding A 2nd Subscrber Makes My Merge Replication Blow Up.

Aug 25, 2006

I have two machines running SQL Express that I am attempting to use as proxies for a client application as the security admin has deemed that outside clients can not connect directly to the main DB servers.

When I have one of the SQL Express boxes subscribed to my SQL 2005 standard server, everything is happy, but when I attempt to add the second SQL Express box as a subscriber, everything seems to blow up.

OK, I'm setup as a merge with updates and no filter since I want all of the servers to get all of the data. I'm also on a non-standard port but have my aliases setup, and I don't have any connection issues. When I run the subscription wizard from the management studio, it comes back with "Success", but about 2 seconds later, I get errors on both subscribers from the replication monitor. Oh, I am aslo setup for push subscriptions and am setup to run continuously.

Here is the error message from the rep. monitor.

Error messages:


No subscription is on this publication or article. (Source: MSSQLServer, Error number: 14050)
Get help: http://help/14050

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001

What I find odd is that It only dies when I try to add a second subscriber, and then it kills both of them. Any help or ideas on where I should look would be most welcome.



Thanks!





View 4 Replies View Related

FTP Task. / Validation Makes Mainframe Downloads Impossible.

Jul 24, 2007

When attempting to use the FTP task to download a file from a Mainframe system the task fails stating the filename as invalid because it doesn't begin with a "/".

Adding the slash to the front of the file name causes the mainframe to be unable to locate the file.

Commandline version of FTP.exe mimicks this behavior by working perfectly when the filename has no slash, and being unable to find the file when the slash is present.

Why does microsoft force a filename to start with a "/" and is there a way to make SSIS skip the validation phase for the FTP task?

View 7 Replies View Related

Any Error In A Data Flow Task Makes My Package Stop

Apr 10, 2008

Hi, this is my first post and I'm relatively new to SSIS so please go easy on me.

Without going into too much detail about it, I've set up a simple SSIS package which does this in a nutshell:

Foreach loop picks up all *.xls files in a given folder
1 - Puts the name of the current spreadsheet into a variable
2 - File System Task copies the current spreadsheet ("abc.xls") to a file called "work.xls"
3 - Data Flow task performs data extraction on "work.xls" and puts it into a SQL server database
4 - File System Task moves "abc.xls" into a "success" folder
Continues with loop - move onto next spreadsheet

This works fine, so long as the spreadsheets all have the same number of columns.

As soon as one of them has a column missing (believe me, this will happen - we're dealing with users here) the package falls over at step 3.

When the package comes across an erroneous spreadsheet, what I'd like to do is move the offending file to a failure folder (making step 4 either a success or failure file move) and carry on with the next one.

I know that you can have an error path (the red line) from any step within the dataflow task, but this doesn't help me because the error lies in the structure of the spreadsheet and not the contents.


I've already come up with a work around whereby each file is moved into the failures folder just after step 2, then moved from the failures folder into the success folder at step 4.

This almost gives me what I want, although of course the package still falls over whenever it encounters a dodgy looking spreadsheet.

Is there any way that I can get the package to do what I'm after?



Many thanks,



Simon

View 1 Replies View Related

Executing A Sp Via ADO Which Makes A Reference To Linked Server Table Results In Error

Oct 17, 1999

Greetings,

Facing a strange problem, but obviously not expected earlier on, I am trying to execute a stored procedure via ADO which refrences a linked server table and I get an error specifying
"
OLEDB provider SQLOLEDB does not contain table "<DatabaseName>"."<owner>"."<tableName>"
"

Has any one else encountered this problem before ?
regards
Govind

View 1 Replies View Related

SQL Server 2012 :: Find Trend How Employee Makes Sales Every Month?

Jun 7, 2015

How do I find sales trend of an employee via comparing current month and previous month sales?

I got so far query upto following,

;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,

[Code]....

I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

View 3 Replies View Related

Master Data Services :: Setting Read Permission On Entity Attribute Makes Model Disappear

May 21, 2013

My company is new to MDS. I am trying to set an attritube in an entity to read only so the users can't change the value in that field. When I did that, the whole model disappeared. I thought I had deleted it by accident so I created a test model and tried to do the same. The test model disappeared. This time, before saving the new settings I took a snapshot. After saving I took another snapshot. You can see that the whole model is gone (zz_RN_Permissions_Test). I tried every other coworker with admin rights and nobody shows it on the Models list. The behavior on the Excel add-in is correct. I can't change any values on that column. But I need to keep the models available.

See before and after snapshots below.

View 8 Replies View Related

Date Diff

May 14, 2007

I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1Syntax error converting date time from character string". can someone please help.SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime),     CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime))    AS StopMinutes,            BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA,            BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME,        OQTCOD, BAADES, PCS, WGT, Tractor, Driver          FROM    dbo.JCI_Delivery_Report  

View 3 Replies View Related

Diff B/w Two Dates

Dec 11, 2000

Hi,
i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs.
so i want to get the difference of start and end datetime=Diag_Hrs.
here i am using the below stored proc.
but i am getting only the hours or minutes or seconds.
so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds.
for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs.
so how we'll get this.
pl help me out asap.
Thanx
reddy

select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table
--where Gpc_no=@GPC_no


--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table
-- where Gpc_no=@GPC_no

select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table
where Gpc_no=@GPC_no

View 3 Replies View Related

Diff. Backup

Oct 24, 2000

Can i restore a diff. backup alone without a complete backup?

(what i am trying is this.....there are two different servers at two diff. places....i need to have both servers in sync. at all times.modifications will take place in one server and the modifications have to be reflected in the other server.i could not go for replication as the servers cannot be connected.sending complete backups daily will be a overhead .hence planning to take complete backup once and send diff. backups alone on subsequent days to the other server.
how can i achieve this?)

View 1 Replies View Related

Diff Backup Help

Mar 22, 2006

Dear all
I am new to the MS SQL, my problem is as follows.
I am having a online database on sql 2k.
every 15 days we have to give payout from our system, so we
have a offline server in our office, we take the complete backup of that day & restore the same on the offline server,
& start the payout process.
The problem is that the full backup is a big file & take a lot of time for downloading from online server.
is it possible that we take on diff. backup of that day & will restore the same on the offline server so the file will take less time to download.
but my offline backup is 15 days old, will that update all the records or not?


regards
Abhijit

View 4 Replies View Related

Database Diff?

Jan 6, 2004

I have 2 databases:
1 from production and 1 from development.
None of the developers kept a changelog so i need to know what has changed (or what is different between the 2).

Any Ideas on how to do this?

View 7 Replies View Related

Diff Backup

Jun 11, 2008

In playing with differential backups, i have taken a full last night, 17GB, took my first diff today 16.5GB, took another diff right after, it was still 16.5GB, took another diff right after 16.5 GB. This database has 0 activity during the day, the import takes place @ night, and select's are done to it during the day. Any help would be greatly appreciated

View 3 Replies View Related

3 Fks Extrct Frm 1 Tbl Into One Row On Diff Tbl

Nov 20, 2007

Hey guys,
m new to SQL i mean really new so i appreciate all the help i can get on this as soon as possible.
Is it possible to extract 3 foreign keys from a single table into one record/row on a different problem ?
Thnx guys hope to hear soon

View 7 Replies View Related

Diff Between SQL 2005 RTM & SP2

Nov 29, 2007

Hello,

I wanted to know the difference between SQL Server 2005 ( RTM) Version and the regular SQL Server 2005 SP2. And does the RTM version have any service packs.

Thanks

View 2 Replies View Related

Database Diff?

Jun 6, 2007

Are there any tools/utilties that can diff 2 sql server databases. I know the DB Pro Edition of VSTS can do it, but its hard to justify a jump from my $800 copy of VS2005 Pro to a $5k-6k version of VS just to get database diff. The tablediff looks promising but I need it to do SPROCS and SFUNCS too. Any helps or recommendations would be appreciated.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved