(I wish I could have come up with a more descriptive title... suggest one or edit this post if you can name the type of query I'm asking about) Database: SQL Server 2000 Sample Data (assume 500,000 rows): Name Candy PreferenceFactor Jim Chocolate 1.0 Brad Lemon Drop .9 Brad Chocolate .1 Chris Chocolate .5 Ch...
how can we get the restore in sql server 2000 of sql server 2005 data ...
I am using the Entity Framework in a web application that utilizes SQL server 2000, 2005, and 2008. When I create a new EDMX file using anything other than 2008 (version of the first edmx created) I receive error 0172: All SSDL artifacts must target the same provider. The Provider 'MyDatabase' is different from ' MyDatabase ' that was en...
I am using MS ACCESS 2003 TABLE NAME –> tmp_cardevent PERSONID CARDEVENTDATE CARDEVENTTIME 5008 20090805 080000 5008 20090805 140000 5008 20090809 180000 5008 20090809 220000 3405 20090805 080000 3405 20090805 180000 3405 20090809 070000 3405 ...
Using MS Access 2003 My query. select a.personid, a.cardeventdate as newday, a.intime as intime, max(b.cardeventdate) as prevday, b.outtime as outtime from ( select personid, cardeventdate, min(cardeventtime) as Intime, max(cardeventtime) as outtime from ( SELECT T_PERSON.cardno, T_PERSON.NAME, T_TITLE.TI...
This problem has bugged me so many times and i have now decided to try and find the proper solution for it, instead of my long-winded/dirty/horrible way I always revert to (copying the sql statement) The table has a date column with a default value of NULL Now what i need to do is pass a value (-1,0,1) to an sql statement which determi...
I understand that when adding a column to a table containing data in SQL server, the column must have a NULL option or a default. Otherwise what would SQL Server pad the new rows with? I am at a loss as to why I can't add a NOT NULL column to an empty table however. I have tried this on two instances of SQL 2008 and one instance of SQL ...
I have to develop database development standards for our organisation for SQL Server and any code that interfaces to it. The code used can be anything from .NET code to VBScript to SQL Server Jobs. Does anyone have a good link for this kind of thing? My quick list is follows: 1) Naming Conventions -- Stored Procedures usp_AppName_SP...
I know in SQL Server 2005 we could do something like: DELETE TOP 10 FROM tbX where X = 1 What could be the query to do the same in SQL2K, considering that the primary key is composed by two FK columns? ...
Hello, I am trying to write a query that will return only the most recent results. The table I am pulling information from has no uniqiue columns, and contains information on rate changes so for any particular client there can be several rate changes - I only want the most recent rate change for each client. The structure is: mrmatte...
In SQL Server 2000, how can I find out what indexes are being used? I can find a list of all the indexes easily enough, but how do I know which ones can be removed because they are no longer used? I don't want to slog through all the sql used on our server running explain plans to find this out and also risk missing something. ...
I'm trying to update an NText field in SQL 2000 using Classic ASP. Here is the code I'm using to do it. Any one have any pointers on how to maybe speed it up? Or am I stuck with it. set Cnn = server.CreateObject("ADODB.connection") Cnn.ConnectionString = Application("Cnn_ConnectionString") Cnn.open set rs = server.CreateObject("ADODB....
It seems that some scripts generated by Enterprise Manager* (or not, it doesn't matter) created check constraints WITH NOCHECK. Now when anyone modifies the table, SQL Server is stumbling across failed check constraints, and throwing errors. Can i make SQL go through all its check constraints, and check them? Running: sp_msforeachta...
Hi, I am using this query: SELECT DISTINCT pat.PublicationID FROM dbo.PubAdvTransData AS pat INNER JOIN dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID WHERE (pa.AdvertiserID = 31331) AND (pat.LastAdDate > 7 / 1 / 2009) ORDER BY pat.PublicationID And it returns 0 results. What I am trying to d...
I got an app running on my SQL Server that is starting to slow down on a specific task. I ran SQL Profiler and noticed that the following query is taking an enormous (1-2 minutes) amount of time. I don't have access to the code to change the query. Is there anything I can tune/change in the database? The PC10000 table in the statemen...
I am trying to modify an exisiting SPROC in SQL 2000. The original SPROC used a FuncView as part of a a cursor. The new report does not require the cursor, but I am unsure how to execute the FuncView outside of the cursor statement. The first example below is the cursor part of the original code. The second is my modification....with...
I've been tasked with encrypting several years worth of sensitive data (don't ask) stored in a SQL Server 2000 Database. I need to encrypt both historical data and come up with a process to encrypt and decrypt new data as it flows through the system. Edit: The processes that produce the data are not the same as the process that will co...
I am trying to import a new certificate because our old one is going to expire very soon. The certificate is ok and valid and works for IIS. Unfortunately not SQL Server won't startup with Encryption turned on. I have updated the current Thumbprint in the registry In addition, if we revert back to the old key with the old thumbpr...
Hi, I have a section of code that runs on a SQL 2005 server as: SELECT e.[ElementName] , ISNULL(ElementValue, ElementDefaultValue) AS Value FROM [cx_Element] e WITH ( NOLOCK ) LEFT JOIN [cx_ReportStyleElements] rse WITH ( NOLOCK ) ON rse.[ElementId] = e. [ElementId] ...
We have a SQL Server 2000 production environment where suddenly (ie. the last 3 days) something has caused the tempdb data file to grow very large (45 gigs with a database which is only 10 gigs). Yesterday, after it happened again we shrank the database and ran the major batch processes individually without any problems. However, this mo...