Hi all
In a new installation, if the data directory is stipulated as d:data, it
installs all
data in d:dataMSSQLdata.
I need to place the data in d:mssql7data to incorporate backsup from old
data backusp
Any ideas ?
Cheers
Ray
Can you install Sql Server 2000 Developer Edition with MSDE 2000 release A already installed?
View 2 Replies View RelatedOn my SP3 db_name(0) and db_name(1) are returning the same thing. Can anybody else try this? Beta of Yukon is doing the same thing...But the following code returns 1: select db_id(db_name(0))
View 3 Replies View Relatedwhy these two SQL codes return same result 0?
select CASE WHEN NULL IN ('123') THEN 1 ELSE 0 END AS Test
select CASE WHEN NOT NULL IN ('123') THEN 1 ELSE 0 END AS Test
Most of our sql servers ar still sql 2000. Our programmers created many sql 2000 database diagrams using EM. But they can not access them under sql 2005. (They now have only sql 2005 tools installed on their boxes.)
Question: can we reinstall the sql 2000 client tools on their boxes without affecting the current sql 2005 install on their boxes?
Question: is there any workaround negating the need to ihstall the sql 2000 client?
TIA,
barkingdog
Run the following: declare @s1 varchar(10), @s2 varchar(10) declare @t table( recordid int identity(1,1)not null,field char(1)not null) insert @t (field) select 'a' union select 'b'select @s1 = '', @s2 = ''select @s1 = @s1 + field +',' from @t order by 1select @s2 = @s2 + field +','from @t order by recordidselect[Weird]=substring(@s1, 1,(datalength(@s1)-1)),[Not]=substring(@s2, 1,(datalength(@s2)-1))
View 2 Replies View RelatedAt my current workplace, whenever I check table sizes using the'reserved' column from sysindexes, or sp_spaceused, I get a total forall user tables which exceeds the physical size of the database.Running sp_spaceused with no parameteres, I get a NEGATIVE value forunallocated space in the database, but only see this if I return theresults in GRID format in QA (text format gives less output):DatabaseName DatabaseSize Unallocated Spacexxxxx_xxxxx11502.38 MB-4874.80 MBReserved Data index_size unused16602800 KB7013752 KB2381904 KB7207144 KBIf I re-run sp_spaceused with the @updateusage='TRUE' option, thisoutput gets corrected to:DatabaseName DatabaseSize Unallocated Spacexxxxx_xxxxx11502.38 MB2773.76 MBReserved Data index_size unused8770680 KB6928168 KB1808096 KB34416 KBwhich shows a substantial difference in the Reserved/Data/Index/Unusedsizes.This happens every day - any ideas about:1) Why this might be happening on such a large scale, and2) Is it conceivable that these discrepancies in space allocationinformation could be causing performance problems? I can imagine thatif the database is trying to locate free pages on extents yet itsinternal view of these doesn't match reality then this could impededata insertion.Offers anybody?
View 2 Replies View RelatedI've sure I have a problem that hasn't been addressed on the forum exactly. I have a SQL 2000 database that the SSIS designer sees, meaning it is a member of the 'Server Explorer' group. I am trying to update my SQL 2005 database based on values from a SQL 2000 database table using an OLE DB Command data flow transformation. I use a SELECT query in the OLE DB Source control to get the required columns. I use an UPDATE statement to identify where to place the columns retrieved from the source. I get an error that tells me that the update statement fails because it can't identify the SQL 2000 database I am trying to use. The highlighted part of the error is something I haven't seen in the similar posts I've read. And, it's ironic because the server is actually 'linked'.
Error 1
Validation error. Import Moving Averages and Open Interest Data: OLE DB Command [3411]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not find server 'SIR-Research' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.". Prices_MA_OI.dtsx 0 0
Here is the SELECT statement:
SELECT
Equity_Moving_Averages.m_d_y,
Equity_Moving_Averages.Ticker_symbol,
Equity_Moving_Averages.MA_10, Equity_Moving_Averages.MA_20, Equity_Moving_Averages.MA_30, Equity_Moving_Averages.MA_40,
Equity_Moving_Averages.MA_50, Equity_Moving_Averages.MA_80, Equity_Moving_Averages.MA_100, Equity_Moving_Averages.MA_160,
Equity_Moving_Averages.MA_200, OI_Data.OI_Call, OI_Data.OI_Call_Pricewgt, OI_Data.OI_Put, OI_Data.OI_Put_Pricewgt, OI_Data.PCRatio,
OI_Data.pcratio_pricewgt, OI_Data.PctRank365, OI_Data.PC_Diff
FROM Equity_Moving_Averages INNER JOIN
OI_Data ON Equity_Moving_Averages.m_d_y = OI_Data.m_d_y AND Equity_Moving_Averages.Ticker_symbol = OI_Data.Ticker_Symbol
--
Here is the UPDATE statement:
UPDATE [dbo].[Equity_Price_DIM]
SET [10day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_10
, [100day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_100
, [160day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_160
, [20day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_20
, [200day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_200
, [30day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_30
, [40day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_40
, [50day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_50
, [80day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_80
, open_interest_call = [SIR-Research].internet.dbo.OI_Data.OI_Call
, oi_call_priceweight = [SIR-Research].internet.dbo.OI_Data.OI_Call_Pricewgt
, open_interest_put = [SIR-Research].internet.dbo.OI_Data.OI_Put
, oi_put_priceweight = [SIR-Research].internet.dbo.OI_Data.OI_Put_Pricewgt
, oi_put_call_difference = [SIR-Research].internet.dbo.OI_Data.PC_Diff
, oi_put_call_ratio = [SIR-Research].internet.dbo.OI_Data.PCRatio
, oi_pcratio_priceweight = [SIR-Research].internet.dbo.OI_Data.pcratio_pricewgt
, oi_percent_rank365 = [SIR-Research].internet.dbo.OI_Data.PctRank365
FROM [dbo].[Equity_Price_DIM] INNER JOIN [SIR-Research].internet.dbo.Equity_Moving_Averages
ON [dbo].[Equity_Price_DIM].prices_market_date = [SIR-Research].internet.dbo.Equity_Moving_Averages.m_d_y
AND [dbo].[Equity_Price_DIM].prices_ticker_symbol = [SIR-Research].internet.dbo.Equity_Moving_Averages.Ticker_symbol
LEFT OUTER JOIN [SIR-Research].internet.dbo.OI_Data
ON [dbo].[Equity_Price_DIM].prices_market_date = [SIR-Research].internet.dbo.OI_Data.m_d_y
AND [dbo].[Equity_Price_DIM].prices_ticker_symbol = [SIR-Research].internet.dbo.OI_Data.Ticker_Symbol
--
Any help will be greatly appreciated.
Dave W.
Hi,
I have been trying to use the Clustering algorithm for Anomaly Detection, but it has been quite difficult to get correct results for PredictCaseLikelihood.
Using a very large number of clusters helped, but there are other problems.
The algorithm does not take into account all the columns in my nested table, but only uses Exists/Not Exists for the nested table key column. In my case the nested table contains also a column with a count of key attribute.
I am about to abandon the Sql Data Mining.
Does anyone have more insights into tuning the Clustering algorithm for anomaly detection?
Thanks
Hi,
I'm would like know if the analysis service data mining enables to detect anomalies from "normal" behavior/patterns of data (1), and alert about such anomalies when detected (2). both above sql server relational DB (3).
Thanks,
Eyal
I am doing monthly reporting for whole months, all starting on the 1st of each month and finishing on the last day of the month
I use dateAdd to calculate the end of the month - so I add 1 month and subtract 1 second (start time is always 00:00:00).
--dFrom is Jan 01 2008 00:00:00
select @dTo=dateadd(ss,-1,dateadd(mm,1, @dFrom))
when I print dTo it is not Jan 31 2008 23:59:59 but Feb 01 2008.
Why is this?
Only starts to behave when I set the number of seconds I am adding from -1 to -31.
Time of date is unimportant: as storing all dates as midday (any data type which ONLY supports date? not interested in the time really).
Subtracting one minute from midnight works fine, but that said: I am curious to understand why I am getting the funny behaviour above.
Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).
All three maintenace plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.
Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run
plan name
task name
duration
log type
log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
Thanks,
Ed
To set up the problem, paste this into QA:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[WorkOTRate]GOCREATE TABLE [dbo].[WorkOTRate] ([TimeFrom] [smalldatetime] NOT NULL ,[TimeTo] [smalldatetime] NOT NULL ,[RateMultiplier] [float] NOT NULL) ON [PRIMARY]GOINSERT INTO dbo.WorkOTRate (TimeFrom, TimeTo, RateMultiplier)VALUES ('18:00:00', '23:59:59', 1.2)SELECT TimeFrom, TimeTo, RateMultiplier FROM dbo.WorkOTRateThis gives the following result:1900-01-01 18:00:001900-01-02 00:00:001.2So, it's storing the time 23:59:59 as midnight. That's odd.(NOTE: If you rescript the table using datetime instead ofsmalldatetime types, the data are stored correctly.)It gets worse (or better, if you like perversity).If I go to Enterprise Manager, right-click on WorkOTRate and select"Open Table" -> "Return All Rows" I get:01/01/1900 18:00:0002/01/19001.2So, I bite the bullet and change the two column types to datetime,clear out the old data and run the INSERT again. The data looks betternow.Go back to the view in EM.If I put the cursor in a new row, and type into the TimeFrom column18:30:00 and the TimeTo column 19:30:00 and the RateMultiplier column1.3, and refresh the data by pressing the red shriek !, I get this:01/01/1900 18:00:0001/01/1900 23:59:591.218:30:0019:30:001.3If I re-run the SELECT from the QA, I get this:1900-01-01 18:00:00.0001900-01-01 23:59:59.0001.21899-12-30 18:30:00.0001899-12-30 19:30:00.0001.3Is it just me, or does this seem to be remarkably inconsistent?Edward
View 2 Replies View RelatedHi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).
All three maintenace plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.
Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run
plan name
task name
duration
log type
log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
Thanks,
Ed
Background: We have SQL Server 2005 x64 running on a quad-core (dual dual-core) machine with 16GB of RAM. The database is about 10GB in size and we execute around a million stored procedures a day on it. Our application uses about 1000 different stored procedures on this machine. The application is a transactional B2B web-app with about 2000 users.
The problem we have is a really odd one that I can't seem to find much information on. We have a small number (3-4) of stored procedures that's exibiting this problem.
The stored proc in question takes on average 100ms CPU time to execute. It's a fairly complex stored proc, about 300 lines long, 6-7 select statements and it uses temp tables. No updates / inserts except for on the temp tables. It's executed about 5000 times per day. About once a week, though, execution times will suddenly jump up to 3000 ms average. This happens randomly during the day, although it seems to happen more often on Monday mornings (the DB is mostly unutilized over the weekend)
To fix this, I force the DB to recalculate the execution plan by adding / removing (depending what I did last time around) the line 'set arithabort on' at the top of the stored procedure. I have no idea why this works, but it does. Within seconds of changing it, the stored proc execution time will go back to it's normal range of 60-150ms.
I've tried setting the execution plan of the stored procedure but I can't get it to work - the execution plan is very long and I don't know how to debug the error I get.
What is happening? This happens with a couple of stored procedures - usually the more complex ones. Has anyone seen anything like this?
Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
Hi Folks,
Can We install SQL Server 2000 Enetrprise Edition on Windows 2000 Professional , i tried but gives me an error.
Is there any turnaround or any SP needed. Pls let me know how can we install SQL server 2000 Enetrprise Edition on Win 2000 professional.
Thanks in Advance
Kumar
Hi,
I 've re-install SQL Server 2000 SP3 just to change password
And while installation I got an error that the instillation fails the message as the following:" setup Failed to configure the server, refer to the server error logs and setup error logs for more information. "
Any idea,
Hi
I'm geting the following error when trying to install SQL 2000 on a new server:
warning the server and client code pages do not match - some ansi cahracter mappings may not not be possible
any ideas??
Paul
I am attempting to install SQL 2000 onto a new Win2K Advanced server and it fails. Event log shows event id:4097 with an exception error @ c0000005. Install progress achieves 99% and just hangs at that point. I cannot find anything relating to this issue, any ideas?
View 1 Replies View RelatedJunior Member
Posts: 2
Joined: Jun 2002
Monday July 01, 2002 11:00 AM (NEW!)
I am installing SQL 2000 on a brand new server. Once installed I will need to restore my db's from our current SQL 7.0 server. I know I can restore the user db's. I've read that you can not restore the MSDB and master db's. So What steps would I take to make sure I get all the info in these db's? I found an article on how to transfer logins and passwords, so that should get me my logins. I'm guessing I'll have to script up my packages /jobs somehow. How do I go about doing this? And what else am I missing that I will need to do in order for my master/msdb to contain everything that it contained on the SQl 7.0 server?
Also should I make the server name the same as the server name on the current box or do I have to make it another name? I'm talking about under the SQL server group.
Also I would like to keep the 7.0 version up for a bit and just make sure the 2000 side is working the way I want to . Our db are datawarehouse db that we use with another software package. So I will need to make changes there before I can totally turn it over to use the SQL 2000 db's One I've done this then I'll be ready for the switch. I will then restore the SQL 7.0 db again on the sql 2000 server and should be ready to go at that time.
What else am I missing. Any tips/precautions?
Hi, I have to install SQL Server 2000 on NT (don't know the version yet)...and I am fairly new to this. Please point to some online documentation for the install or if anybody has created their own documentation which they would like to share that would be great.
Thanks.
dollar
I am trying to install sql 2000 evaluation version from the microsoft website and am having a "fun" time! I cant seem to get the exe to extract...when it nears finishing it prompts me that I need to clear 478 MBs on C: but there is AMPLE room on the drive. Am I missing something, or has anyone ran into something similar before. Thanks for any help!
Adam
Hi.I just downloaded and installed Sql 2005 from the MS site.I see that my instance of Sql 2000 is still on the same computer.I assumed that Sql 2005 would upgrade but it didnt. Should Isimply delete my instance of 2000 or will this cause complications?Sorry - newbie question.
View 1 Replies View RelatedHello there!I am trying to install MS SQL 2000 Server on Windows XP Home edition.I found in Microsoft that the above installation is possible.But when i run the installation, i am getting a black screen that iscloses after a second. Nothing else happens.Any ideas how to troubleshoot this?Thank you
View 4 Replies View RelatedI'm trying to install a default instance of Developer edition on alaptop. I'm getting the following promblems shown in sqltp log. Thelaptop has previously had the SQL Developer installed but has sincehad OS installed.I've tested named pipes to another server (net view, net use) and itseems to be working.any ideas ?Starting Service ...Latin1_General_CI_AS-m -Q -T4022 -T3659Connecting to Server ...driver={sql server};server=J6Y741J;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver][Named Pipes]Connection broken.[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionRead(GetOverLappedResult()).driver={sql server};server=J6Y741J;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver][Named Pipes]Connection broken.[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionRead(GetOverLappedResult()).driver={sql server};server=J6Y741J;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver][Named Pipes]Connection broken.[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionRead(GetOverLappedResult()).SQL Server configuration failed.
View 1 Replies View RelatedHi,
Long story, Ihad SQL 2000 happily installed and running for a long time. Attempted to load SQL 2005 alongside, but in retrospect see that I used the same install directory as SQL 2000. Found that the SQL 2000 databases still ran, in that I was able to run applications that rely on them. Enterprise manager ans Query Analyzer are still in the start/program list, but when I try to open them they say that the DSN isn't there, and the odbc driver is missing. Also if I try to configure the system SQL DSN in ODBC datasources I get the same message. I guess the SQL 2005 install has scrambled the ODBC setup.Tried deinstalling SQL 2005, but the problem persists. Tried reloading mdac-typ, same problem. Tried reinstalling SQL2000, same problem. Tried fully deinstalling SQL2000 from Add/remove program, said it completed, but the shortcuts are still there, the applications that use the db's still work, only it no longer appears in Add/Remove programs.
Is there a command line string to manually remove it, or where do I go to repair my mdac, or is this what I actually want to do?
Thanks in advance
Hugh Esler
I installed SQL 2005 developer edition in vista business OS with SP2. it is working fine now i want to install SQL 2000 standard edition. when i am putting SQL 2000 CD into my system it is not run even setup.exe not working.
Thank You.
Hi all
I have a machine running SQL2000. And I have to share the machine to run another application that runs .NET2.0 and J#2.0.
Will it have any impact on the existing SQL 2000 server? Any experience could share?
Thanks
Dear Community,
I have recently downloaded the MSSQL 2000 Evaluation package from microsoft website,but i can't get it to install.I run the autorun normally,but when i select the option install database server nothing happens.I am trying to install it on Windows XP Service Pack 2 Edition 5.1....As this would help,in 3-4 hours of computer inactivity,i see the setup loader but nothing happens,it says not responding.
Thanks in advance,
Dimitris.
Most of the posts here for co-existence of sql 2005 and sql 2000 are for upgrades to 2005 or installing sql 2005 on a box already having sql 2000. This is a bit different..
I have an application installed on sql 2005 sp1, on a default instance, no other instances installed. Because of issues the application is having with sql 2005, the vendor is recommending install of sql 2000 on the same box ('should not have any problems' they say), then script out the user databases in SQL 2005 and back in to the sql 2000 instance.
Has anyone tried this (installing sql 2000 after install of sql 2005) or can point me to an article about it? I've googled around and searched the knowledgebase to no avail.
thx
My dev. machine already has SQL Server 2005 (sp2) installed.
Can I install SQL Server 2000 on this machine as well (as a named instance) ? Or does 2000 need to be installed before 2005 ?
Thanks.
Have app that will only run on Sql 2000, am currently using MSDE, we have outgrown and need to upgrade to 2000. I purchased SQL 2005 open license, I was told that 2000 wasn't availabe and that I could intall SQL 2000 from the 2005 cd's I received. How do I do this? They sent me all the 2005 cd's and SP4 for SQl 2000. I installed 2005 but did not see any way to roll back to 2000.
View 1 Replies View Related