hi,
I just realized that this must be a difference between sql server 2000 and 2005.
For example, the query is like this:
declare @n int
set @n = -1
select @n = employeeid from employees where companyID=1 and idnumber='1-1'
--select @n
if(@n is null)
......
If there is no such employee, in 20002, @n will be null, but in 2005, @n is -1, not null. Am I right?
So, for this piece, I have to do twice on the same where, like
if(exists(select * from employees where companyID=1 and idnumber='1-1'))
select @n=employeeid from employees where companyID=1 and idnumber='1-1'
else
set @n = null
Any better way?
I have function that returns a table of information about properties. Thedata comes from three different tables -- addresses (called PropertyID),property characteristics, and events concerning those properties (sales,appraisals, etc.), plus a table that maps one representation of propertytypes into another. The records are selected on the basis of location(longitude & latitude), property type, event type, and a range ofevent dates (upper and lower date specified). There are tens of millionsof records of all types, and almost any location, property type, event typeand date range will yield records.The heart of it is a cursor that selects records from joins on this basis:SELECT <a bunch of fields>FROM Property dJOIN PropTypeMap ptm ON ptm.PropertyTypeID = d.PropertyTypeIDJOIN PropertyID a ON a.PropID = d.PropIDJOIN Event e1 ON e1.PropID = d.PropIDLEFT OUTER JOIN Event e2 ON e2.PropID = d.PropIDWHEREd.LastSaleDate >= @LoDateAND a.GeoLongitude BETWEEN @LowerLon AND @UpperLonAND a.GeoLatitude BETWEEN @LowerLat AND @UpperLatAND ptm.PropCategory = @PropTypeAND a.GeoMatch <= @MinGeoQualityAND e1.EventTypeID = @SaleEventTypeAND e1.TransactionType = 'R'AND e1.EventDt BETWEEN @LoDate AND @HiDateAND e1.EventAmt > 0AND e2.EventTypeID = @AssessmentEventTypeAND e2.EventDt <= @HiDateAND e2.EventAmt > 0Each property has one PropertyID record, one Property record, and N Eventrecords (average perhaps five).What is the mystery? If @HiDate, which is the upper end of the time window,is 2002-11-08 or earlier, nothing is returned. If it's 2002-11-09 or later,oodles of records are found. I get the same query plan for either one, andbased on the content of the data, they should return almost exactly the sameset of records -- exactly the same set in almost all cases, in fact.Is 2002-11-08/09 some sort of magic dividing point? I have replicated thison the large database and on a smaller test version on another SQL Server.(SQL Server 2000) I dropped the indexes and tried it, andthe same thing happened. This is driving me crazy!
I have an Access DB with about 50 tables. When I run the upsize wizard all bur four tables import beautifully, data and all. Four of the tables fail with the message 'Table was skipped, or export failed'
When I try to IMPORT the data I get 100 or so messages like this one:
Warning 0x80047076: Data Flow Task: The output column "AssessPlanPHPID" (23) on output "OLE DB Source Output" (11) and component "Source - tblAssessPlan" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. (SQL Server Import and Export Wizard)
And the import fails. Note that the upsize creates the tables just fine, it just won't import the data.
By default, the Upsizing Wizard transfers all indexes as nonclustered indexes. You can modify the Upsizing Wizard to transfer the primary key index to a clustered index. To make this change, start Microsoft Access and open the upsizing wizard library database. For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the filename is Wzcs97.mda. When the database is open, click the Modules tab and open the UT_ModUserConstants module. Search down to the UT_CLUSTERED constant. Change the default value from False to True.
<<< end quote Question: I am using ACCESS 2002 upsize wizard, I searched my computer for *.mda and could not find any wzcs*.mda. How do I set it so that it creates clustered-index instead of non-clustered-index?
Could someone give me the difference between "Sql Mobile 2005" and "Sql Compact 2005". At first I thought they were the same, that this was just something of the change in naming the server has had the last months. My first guess was that Mobile would be the new one, but I have 'accidently' downloaded the Compact and what seems the System.data.SqlServerCE.dll is newer.
So is there a difference? If yes, what are they. If not, is the "compact" then the latest version and the name to be used (since on the site only "mobile" is mentioned).
I am trying to use a condition in a query with CTE but I got an error.It is simple enough but I just cannot get it work. Any help will be greatly appreciated! Msg 156, Level 15, State 1, Line 8Incorrect syntax near the keyword 'if'. declare @i as int set @i=1; with CTE_A as ( select 'B' )
if @i=1 ---this part get problem select * from CTE_A else select 'B'
We have a stored proc that accepts a date and uses that date in a filter. This procedure worked fine for a couple of weeks then started hanging. Basically, this date parameter is used in a where clause.
Our workaround: when we declare a new variable, set it equal to the parameter, then use the declared variable - that fixes it. Take away the declared variable and use the parameter instead and the proc chokes. But the paremeter and the declare variable are both datetime typed. Weird.
We are worried (partly because it worked without hanging for a while) that we are experiencing a larger issue. We are reporting off a database we denormalize and populate nightly with transactional data. A poor man's warehouse if you will, staging the data in a fast reporting format with the prep done each night. This database is wiped and re-populated each night. We have been testing this process with the same source data, so we are not growing our data at all (net result at end of each night is same data as previous day).
I think the source code here is overkill, but I enclosed the proc anyway. The way it is written below, it hangs. Replace these two uses of @AsOf in the proc with @test and the procedure almost instantly returns data. There is a third use of @AsOf that doesn't affect the issue one way or the other. This line is where the replacement fixes the problem:
--Breaks Where (OpenedDt < DateAdd(day, 1, @AsOf) or ClosedDt < DateAdd(day, 1, @AsOf)) and (PlanType = @PlanType or @PlanType is null)
--Fixed Where (OpenedDt < DateAdd(day, 1, @test) or ClosedDt < DateAdd(day, 1, @test)) and (PlanType = @PlanType or @PlanType is null)
If anyone has experienced this or knows some other symptoms to check for, we are all ears. This is scaring us because otherwise we have a working system on our hands and are near a delivery point. Help would be greatly appreciated.
Here is the complete proc...
ALTER PROCEDURE [dbo].[ClaimCountsByLineOfBusiness] -- Add the parameters for the stored procedure here @AsOf datetime, @PlanType varchar(50)
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @DataFreshness datetime Select @DataFreshness = DataFreshness From metaReportList Where StoredProcedure = 'ClaimCountsByLineOfBusiness'
If @AsOf is null SET @AsOf = Convert(datetime, Convert(varchar(10), GetDate(), 101))
DECLARE @test datetime SET @test = @AsOf
-- Insert statements for procedure here Select PolicyGroup, Lines, SUM(CASE WHEN HasPayment = 1 and OpenClosed <= 0 THEN 1 ELSE 0 END) as ClosedClaimswithPayment, SUM(CASE WHEN HasPayment = 0 and OpenClosed <= 0 THEN 1 ELSE 0 END) as ClosedClaimswithoutPayment, SUM(CASE WHEN OpenClosed > 0 THEN 1 ELSE 0 END) as ClaimsOpen, Count(ClaimSummary.ClaimNumber) as ClaimsReported, AccidentYear, ReportingYear, --Return the value of the parameters in a friendly way (if null) Convert(varchar(10), @Asof, 101) as AsOfParameter, IsNull(@PlanType, 'any value') as PlanTypeParameter, @DataFreshness as DataFreshness
FROM (Select ClaimNumber, Max(OpenedDt) as OpenedDt, HasPayment, PolicyGroup, Lines, AccidentYear, ReportingYear From dbo.factClaimSummary Where (OpenedDt < DateAdd(day, 1, @AsOf) or ClosedDt < DateAdd(day, 1, @AsOf)) and (PlanType = @PlanType or @PlanType is null) Group by ClaimNumber, HasPayment, PolicyGroup, Lines, AccidentYear, ReportingYear) as ClaimSummary
INNER JOIN
(Select ClaimNumber, SUM(CASE WHEN OpenedDt is not null THEN 1 ELSE -1 END) as OpenClosed From dbo.factClaimSummary Group by claimnumber) Status ON ClaimSummary.ClaimNumber = Status.ClaimNumber
Group by PolicyGroup, Lines, AccidentYear, ReportingYear Order by PolicyGroup, Lines END
Wondering if anyone can help with a strange exception thrown while using the SqlBulkCopy class. I am using the class to transfer records from a DataTable in memory (approx 11,000 rows) into a SQL Server 2005 table.
Initially, the WriteToServer method was timing out a la KB913177 (http://support.microsoft.com/default.aspx/kb/913177), however I downloaded the hotfix, which eliminated this issue.
Now, I get a new exception thrown, as follows:
System.Data.SqlClient.SqlException: OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Power_Avg'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
I can't see anything wrong with the data I have. The column "Power_Avg" is of type "float". When forming the DataTable, I cast my data to float expcilitly in C#.
Other things to know:
I am using SQL Server Express (2005) This same code works fine with SQL Server 2000 (MSDE) My code makes all modifications inside a single transaction of type "Snapshot" (I have activated SNAPSHOT READ COMMITTED in the database) I have not activated MARS in the connection
Hi, we have setup synchronous mirroring with witness server.We ran into problem when we stoped the SQLService of Primary Server. Failover Occured and Witness Server made Mirror Server Primary.
The User assosciated with the Database become Orphaned and no one is able to connect to the Database . We recove login failed error for all login for mirrored database. We have to manually run the below comand and reset the password for each login.
1.To Detect Orphaned Users sp_change_users_login @Action='Report'; 2.To Resolve an Orphaned User sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'; GO
Please let me know why sql server mirroring show such strange behavior. If we manually failove every thing work fine.
do any body know the article that can give me berif idea about the difference between sql server 2005 and 2008, acutally i want to move over to 2008. but if the difference is not that much then i m might think about that.
I found an unusual problem between 2000 and 2005 I haven't been ableto decipher from any documentation.The query structure is as follows:select *fromtableA ajointableB b ON a.somekey = b.somekeywherea.type = 'A'and datediff(yyyy, b.someDateField, getdate()) betweena.lowboundary and a.highboundarySome basic facts about the elements and data. The low and high-boundary fields are varchar datatypes. In 2005 (regardless ofcompatibility type I run the database under), the query evaluates theBETWEEN and errors out due to the fact that it is evaluating theDATEDIFF as an integer and finds a non-integer entry in eitherlowboundary or highboundary. I understand and expect this behavior.Obviously, changing the result of the DATEDIFF function to varcharallows the operation to go forth.The odd thing is that there is no "a.type = 'A' " entry, thus thequery wouldn't return anything. In 2000, it seems as though theengine is evaluating the type = 'A' and short-circuiting and in 2005,it is trying to evaluate the entire query OR is there an implicitconversion occuring in 2000 and not in 2005?As I mentioned, the compatibility mode doesn't change how this reacts,but running this on a native 2000 server allows this to happen. Thisparticular code isn't the problem, it's what we might have to contendwith when we migrate this through. Sure, we're going to performregression testing, but I'm concerned about what we would miss.Thanks for any replies.
I have a very strange problem using SQL Server 2005
I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:
Select * from custoemr where EmployeeID = 3
on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..
Can someome please teel me how to preceed in resolving this issue..
I was just trying to get some information on how to move an Access 2002 db to MS SQL. Also, what all do I need to be able to access the db from the web. For instance, query the db for info from a website. I don't have that much experience in SQL but I quiet a few programming languages so i'm sure i'll learn it quick, I usually do. Right now I have a server set up with MS SQL server 2005 with all the other things required for testing over in my own little world. DNS, Active Dir., etc....
I'm trying to do this because a friend of a friend has a business(small businessish) who wants to be able to view reports from queries over the internet. And i'm sure I can do it but i told them to let me give it a test run to make sure I can before we commit.
Can I use Visual Studio 2002 for creating deployment for SQL Express 2005 (like MSDE Deployment toolkit) or it is possible only with VS 2005? I need to create installer, that installs SQL Express 2005 and creates DB (executes sql file). How can I do it better?
--find day,month,year --for day select datediff(d,'01 may 2008',getdate()) -- --for month select datediff(m,'01 jun 2006',getdate()) -- --for year select datediff(year,'01 jun 2006',getdate()) above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively. but I want completely so I can use this in case of expired user in my project. User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize. thanks
Hello, all, I started out thinking my problems were elsewhere but as Ihave worked through this I have isolated my problem, currently, as adifference between MSDE and SQL Express 2005 (I'll just call itExpress for simplicity).I have, to try to simplify things, put the exact same DB on twosystems, one running MSDE and one running Express. Both have 2 Ghzprocessors (one Intel, one AMD), both have a decent amount of RAM(Intel system has 1 GB, AMD system has 512 MB), and plenty of GB offree disk space. MSDE is running on the Intel system, Express isrunning on the AMD system. To keep things fair I use the exact sameDB's and query on both systems. The DB's were created on MSDE so Isp_detach_db'd them from MSDE and then sp_attach_db'd them to Express(this is how MS says to do a "side-by-side" upgrade, so it'sacceptable to do so). After fighting problems in performancedifferences in different situations I have narrowed the problem downto this:Executing a simple select statement with join clause on the databasesyields a difference in execution time that is quite great. Using theExpress Management program I can run the query against either system(MSDE or Express, the two systems are connected via crossover cable toeliminate any network problems/issues). When running the queryagainst the MSDE system (which is over the network) I consistently get<20 ms response times on the query. When running the query againstthe Express installation (which is in shared memory) I consistentlyget 700 ms or longer response times. Both times are for the TotalExecution Time.The query is simply this: select db1.* from db1.owner.tablename as db1inner join db2.owner.tablename as db2 on db1.pkey = db2.someid wheredb1.criteria = 3So, gimme all the columns from one table in one DB (local to theinstallation), matching the records in another DB (also local to theinstallation), where one field in the first db matches a field in thesecond db and where, in the first db, one column value = 3.The first table has a total record count of 630 records of which only12 match the where clause. The second table has a total record countof about 2,700 of which only 12 match up on the 12 out of 630.Even though the data is the same and I've done the detach and attach,and even done the sp_updatestats, the difference in execution time isremarkable, in a bad way.Checking the Execution Plan reveals that both queries have the samesteps, but, on the MSDE system the largest consumer in the process isthe Clustered Index Scan of the 630 record table (DB1 in my queryexample), using 85%. The next big consumer is a Clustered Index Seekagainst the other table (2,700 rows), using 15%.The Execution Plan against the Express system reveals basically theexact opposite: 27% going to the Clustered Index Scan of the 630record DB1, and 72% going to the Clustered Index Seek of the 2,700record DB2.I'm sorry to be stupid but I have this information but I don't knowwhat to do with it. The best that I can tell from this is that thisis the source of my problems. My problems are that on my currentsystems that my clients use the data is returned to them faster thanthey can click the mouse and that the new system (that is, when theychose (or are forced by attrition) to move to Vista and thus Express2005) the screen pop is like 1.5 seconds. This creates poor userexperience. Worse, one process I allow the users to do goes fromtaking 14-30 seconds to over 4 minutes (all on the same machine withthe same OS and version of my program, so it's not a machine or OS ormy app problem).Anyway, I hope someone can shed some light on this now that I've paredit down some.Thanks in advance.--HC
I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.
The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).
I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.
Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.
Problem:Upsize a backend MSAccess 2002 Database to SQL Express 2005Explored:Tried using the upsizing wizard from Office XP(2002), Two tables always get skipped.***! The two tables skipped data only, the tablename and data structure were created.Tried to install UPSize Pro, installation failed.I decide to try it in VWD 2005, here is my code so far but it keeps erroring out. Dim cn As System.Data.OleDb.OleDbConnectionDim cmd As System.Data.OleDb.OleDbDataAdapterDim ds As New System.Data.DataSet()cn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:Documents and SettingsBillMy DocumentsVisual Studio 2005WebSitesWebSite1App_DataData for Database.mdb';Persist Security Info=True")cmd = New System.Data.OleDb.OleDbDataAdapter("select * from Service_Orders", cn)cn.Open()cmd.Fill(ds)cn.Close()
Dim connDest As New Data.SqlClient.SqlConnection("Data Source=WLOCKLAPTOPSQLEXPRESS;Initial Catalog='Data for DatabaseSQLND1';Integrated Security=True")connDest.Open()Dim oBCP As New Data.SqlClient.SqlBulkCopy(connDest)oBCP.DestinationTableName = "Service_Orders"oBCP.WriteToServer(ds)oBCP.Close()connDest.Close() It erors on Line oBCP.WriteToServer(ds)with......System.InvalidCastException was unhandled by user code Message="Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.IDataReader'." Source="App_Web_hb6xyamq" StackTrace: at ASP.xfer_data_aspx.Button1_Click(Object sender, EventArgs e) in C:Documents and SettingsBillMy DocumentsVisual Studio 2005WebSitesWebSite1xfer data.aspx:line 30 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)I'm open for ANY suggestions, I do not have access to DTS, its not in the Express addition. Thanks in advance.Bill
I am using a very basic query in Front Page 2002 against a SQL table inSQL 2000SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)and f_docnumber is a search form field.When I run I getDescription: The precision is invalid.Number: -2147467259 (0x80004005)The field is defined in the table as numberic 9(10,0)Any ideas?
