Hi All
Sorry about that naïve question but for some reason on one of my computers when I connect to Sql 2005 I don€™t have Edit option when right clicking on a view.
If I€™m connecting from another computer using same login it€™s all there.
Could you please help?
My organization is currently migrating about 35 databases from SQL 2000 to SQL 2005. Most of the databases are small with the largest being about 6gb. These databases are not very transaction intensive. We are about half way through as we started the project a year ago. We purchased new hardware for this SQL 2005 so it is a side-by-side migration and it has been going well so far. I made one oversight which I think will come back to haunt us down the line. I installed Windows Server 2003 Standard R2 Edition and of course now I am considering putting more RAM in the server. We have 4 GB of RAM in the server and of course Standard Edition will not address more 4GB of physical ram. I have not seen any memory pressure yet but as databases are migrated over the amount of physical RAM drops. My buffer hit cahce ratio and page life expectancy performance counters are fine so far. The server is a brand new HP DL 380 G5 with with tons of disk and processing power, but only 4GB of RAM. I can't migrate to another server with more ram obviously.
1. What would you do in a situation like this?
2. I could do an in-place upgrade to Windows Server 2003 Enterprise R2. Has anyone attempted an in-place upgrade? I have no applications
on this server, just SQL 2005 Standard.
3. Would the /3gb switch be an option? I have heard mixed reviews on this one.
As far as database optimization, %75 of the databases are third party so we have little flexibility in tuning them or redesigning
tables.
Thanks,
Alex
Ok, here goes....
What exactly does a standard SQL 2005 license cover? I want to have 3 client stations entering data, one IIS serving up data to an unknown number of persons via a web page. Under the 1 server 5 CAL package would this setup be covered? or will I have to go the processor route? I might add that the user accessing via the web service would be on an intranet behind a firewall.
Thanks in advance!
I printed out several pages of documentation regarding the install of 2005. When I tried performing the installation from CD I double-click splash.hta, as indicated in "How to: Install SQL Server 2005 (Setup)". Under Install I see two options:
Server components, tools, Books Online, and samples
Run the SQL Native Client Installation Wizard
The documentation indicates I should be clicking on "Run the SQL Server Installation Wizard", which is not an option. If I select "Run the SQL Native Client..." it appears to be only installing the client and does not follow the remainder of the instructions. I can't find any information on Microsoft's site on how to perform an install from CD.
I'm guessing I should be using the DVD, but has anyone else noticed the "How to" documentation is a little off and have you found any other documentation that is more accurate?
Thanks, Dave
Why did Microsoft release a replacement for MSDE called SQL 2005 Express with Advanced Options without the SQL Agent? I bought into the MSDE and got rid of the other databases we supported now we have to go a create an equivilent to the SQL Agent for a) scheduled jobs b) backup automation. c) etc.
Does anybody really want a database that does not have these built in? I understand I can do these myself but this seems to defy logic to me?
thanks,
Larry
Hi,,
when I open a connection to SQL Server 2005 (c#, VB.NET 2005, SQL Native Client) these setting are set (can see it in Profiler):
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
I would like ARITHABORT to be ON. Have tryied to set it as a defaul in my database, but still the connection has OFF at this setting.
How can I force my C# code to set this option to ON during connection opening?
Thanks,
Przemo
I am looking for a way to replicate databases from an environment using SQL Server 2000 sp4 to machines using either 2000 sp4 or 2005 sp2. The size of the dbs (+3 gb) warrant a transactional model, but the developers opted to not create primary keys for any of their tables and PKs are required for transactional publications (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1627873&SiteID=17&pageid=0#1614178). Creating PKs are, most likely, not an option given the time constraints of the project.
I'm also aware of the fact that merge replication does not work from SQL Server 2000 to SQL Server 2005 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=499634&SiteID=1).
I'm looking for alternative solutions to this problem, regardless of whether it's replication or not. Log shipping was suggested at one point, but I want secondary confirmation on this. Any help is appreciated. Thanks.
UPDATE: Log shipping in SQL Server 2000 is supported in Enterprise and Developer edition. The source server is using 2000 Standard.
UPDATE 2: Log shipping in SQL Server Standard is *possible* but unsupported http://sql-server-performance.com/Community/forums/t/17798.aspx?PageIndex=1. It looks like the original poster was still having problems by the end of this thread.
Which tool is used to maintain packages stored in the MSDB. Do you have to export them as file based to edit them?
View 3 Replies View RelatedHello,
I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:
Cannot show editor for this task
Additional information:
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
I also cannot save any change that I make in the schedule of the plan.
I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.
Any help is most appreciated!
Eunice Griffin
I sure would like a quicker way to get to a script's code, then:
1. double-click
2. ctrl+tab to switch to script item in list
3. click on "Design Script" button.
I'm constantly editing scripts and I would LOVE to be able to right-click, "Design Script", bypassing the silly dialog. I've tried creating a macro with no success. While recording, when I click on "Design Script" from the dialog, it actually brings up the macro script in the editor. This task is frequent enough, that it deserves its own context menu entry.
Any ideas?
Hi!I am able to add records to my SQL 2005 database using bound forms in myAccess application, but if I try to edit any records (not only the newrecords I create) using the same bound forms, I receive a write conflict.For the record, I am currently the only user of this application and my SQLServer database has only one userID. I used this userID to create all ofthe objects in the database and to connect to the database from Access. Thedata sources for the bound forms are linked tables, the record source typeis set to "Dynaset" and the record locks are set to "No Locks". "Opendatabases using record-level locking" is checked.The write error says that another user has changed the current record sinceit was opened (since I'm currently the only user this isn't possible) andasks me to copy my changes to the clipboard or drop the changes.Can anyone suggest what might be causing this? Thanks!
View 1 Replies View RelatedOk...
This worked on the laptop I had last week. That laptop has ceased to be. I have a new one, which may be my problem.
I have a package that takes an Excel Sheet, makes a couple of conversions via the Data Conversion object, and places the rows into my SQL 2005 X64 database.
Here are the major exceptions:
1. [Excel Source [2479]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (THERE WERE NOT)
2. [Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Here is the rub... I am running Vista(Weeeee), and Office 2007(Double Weeeeee). Last week I was running XP, and Office 2003.
Is this a wierd Vista permission problem? The "Class not Registered" message is worrying me. Does SSIS work with Excel 2007 installed?
Xig
All,
If this questions is misplaced please forgive me.
I have a customer who has a Win SBS 2003 Premium installation with a Win 2003 Standard member server running SQL 2000 housing a Practice Management App.
The new release of the Practice Management App only runs on SQL 2005.
My question is what are my options for obtaining SQL 2005 in the most cost effective way? The SBS Premium install was not a candidate for the 'free' upgrade to R2. Is there an 'upgrade' release of SQL 2005 for existing SBS Premium customers?
Any information would be greatly appreciated.
Thanks!
Ray
Hi
Bear with me I am creating my first report using sql server 2005 report wizard.
I have a query that s runs OK and returns name/addresses of companies for me to write a statement report.
My question is , how can only the 'FILLED' address column get displayed onto report and empty address fields are rejected i.e.
Address 1: 12 WoodStock Road
Address 2:
Address 3: Oxford
Address 4:
Address 5: OX10 0AB
therefore the report (page) should only display Address 1, Address 3 and Address 5
I tried to solve by trying to write an expression in 'Edit Experssion' without any joy.
Thanks in advance
Details: OS Vista Ultimate (64-bit), Harware: Intel Core Duo 2 2.13 Ghz, 4 GB RAM, Serial ATA WD Raptor 150GB.
Installed all the service packs for Vista. All hardware configured and running. IIS is running, .Net 2.0 is running. During the install I get green flags across the board for all items. Regardless of what I install, when it gets to installing the DB Engine it throws an error:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Windows NT user or group 'MARK-PCSQLServer2005MSSQLUser$MARK-PC$MSSQLSERVER' not found. Check the name again.. Refer to the server error logs and Setup logs for detailed error information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40CnfgSQL%4080%40x3c29
------------------------------
BUTTONS:
&Retry
Cancel
------------------------------
The url above takes me to a web page with this:
ID:
29521
Source:
setup.rll
I have tried not using the built in system account locally on my machine which I am logged into as with Admin rights. I have tried creating another Admin level account, and using it. I have tried using the accounts listed above specified as "domain account", and have always specified Multi Mode Authentication and set a password for the "sa" account.
No matter what, I get the error above.
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
-chris
Hi,
SQL Server 2005 isn't accepting any connection other than SQL Management Studio from the local machine. However, even that's exhibiting strange behavior (please see below for more details).
I've read through all the common errors regarding enabling remote connections via the SAC tool, making sure protocols are enabled, etc. This case seems to be completely outside. Any advice appreciated.
Thanks,
Elias
[1] Client side:
What is the connection string in you app or DSN? N/A. Can't connect via any remote method, including client Control Panel > ODBC > create DSN, SQL Server Enterprise Manager, third party application.
If client fails to connect, what is the client error messages? Several, depending on method: "server does not exist or access denied", "error has occurred... under default settings, SQL Server does not accept remote connections", etc.
Is the client remote or local to the SQL server machine? Remote
Can you ping your server? YesCan you telnet to your SQL Server? No. However, there's a functional instance of SQL Server 2000 on the same domain which has no problems- telnet also fails to that instance, even locally from either machine using the 127.0.0.1 address. Telnet functions for other ports.
What
is your client database provider? N/A Or/And, what is your client
application? SQL Management Studio and others
Is your client computer in the same domain as the Server computer? Same domain
What protocol the client enabled? TCPIP when I attempted to create the DSN. Not sure what protocol SQL Management Studio uses.
Do
you have aliases configured that match the server name portion of your
connection string? NoDo you select force encryption on server and/or client? No
[2] Server side:
What is the MS SQL version? SQL Server 2005
What is the SKU of MS SQL? Standard
What
is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named
Pipes ] Have tried all three individually and together. Configuration Manager exhibits strange behavior (see below)Does the server start successfully? Server starts successfully when the machine boots. However once it's stopped, it can't be restarted (see below)
If SQL Server is a named instance, is the SQL browser enabled? Default instance
What is the account that the SQL Server is running under? Local SystemDo
you make firewall exception for your SQL server TCP port if you want
connect remotely through TCP provider? Not sure, but SQL Server 200 instance is working OK
Do
you make firewall exception for SQL Browser UDP port 1434? Unknown
[3] Platform:
What is the OS version? Client: Windows XPSP2 Server: Windows Server 2003
Do you have third party antivirus, anti-spareware software installed? Client: Trend Micro Server: UnknownOther behavior:
A separate instance of SQL Server 2000 on a different machine on the same domain is functioning without problems.
Telnet to either SQL Server (functional 2000 or problem 2005) on port 1433 is not possible - even when attempted from the local machine using 127.0.0.1! Telnet to both machines works using other ports.
When creating a DSN from a client machine, problem 2005 instance is in list of available SQL Servers, however connection fails ("SQL Server denied access or does not exist").
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL Server (MSSQLSERVER) service on Local Computer Started and then Stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL
Server (MSSQLSERVER) service on Local Computer Started and then
Stopped. Some services stop automatically if they have no work to do,
for example, the Performance Logs and Alerts Service."
SQL Server Surface Area Configuration for Services and Connections tool does not display Database Engine option as indicated by this MS guide to enabling remote connections: http://support.microsoft.com/default.aspx?scid=kb;en-us;914277
SQL Server communication manager shows TCP, Shared memory, named pipes enabled. However event log seems to show instance not listening via any protocol. Message displayed as: "SQL Server listening on ." (should read: "Server is listening on ['any' <ipaddress> <ipv4> <Port Number>])
Several answers indicated uninstall and reinstall could solve these problems. However, wizard refused to uninstall SQL Server.
Attempted disabling Shared Memory as documentation indicates this protocol gets used first. No luck.
Access through SQL Server Management Studio works, from local machine only.
Able to ping machine by name and IP.
Very often, when I generate SQL scripts for a table, I forget to go to Option tab to click the pk, default, index boxes. Is there a way to permernatly set the whole sql server generate sql scripts options ONCE?
thanks
David
Edski writes "Help please,
below is my sql query, when I run it, I get the right result with one problem. I get same customers with same email many times. I would only like the same customer shown one time. Checking it by email.
SELECT od.pName, od.dIndx AS dindx, od.pIndx AS pindx, od.device AS device, c.Cust_Id AS cust_id,
c.First_Name AS First_name,
c.Last_Name AS last_name, c.Email AS Email, c.Phone AS phone,c.company, h.productCode AS
product_code, DATEDIFF(dd, GETDATE(), h.expirationDate)
AS Difference, h.hotSyncId AS regcode, od.autorenewal AS autorenewal, od.installment AS
installment
FROM Orders_Detail od INNER JOIN
Hot_Sync_ID h ON od.dIndx = h.dIndx INNER JOIN
Customers c ON c.Cust_Id = h.cust_id
WHERE (od.prenewal ='False') and (od.autorenewal = 'no') and (c.newsletter <> 0) AND
(DATEDIFF(dd, GETDATE(), h.expirationDate) = '27') AND (c.company <> 'University of Alberta')
ORDER BY difference, c.Last_Name, h.productCode"
HiI have created a Sql Script through Enterprise Manager for Drop acolumn. By default its creating lot of 'SET' commands. I doubt allthese SET options are required or not. Pls comment on this issueBEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLESET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONALTER TABLE EmployeeDROP COLUMN OrderDetails_IDGOCOMMITDil
View 2 Replies View RelatedHi ,
I could do with a couple of pointers to the best options to acheive my goal, I'm pretty close with the way I've done it, but I feel there is a more elegant solution out their so your help would be most appreciated.
The problem is finding the best way of moving some SQL Server 2000 changed data into sql server 2005. We are only interested in some tables in 2000 (and sometimes just subsets of those). Because there are quite a few tables and the we want to set up a schedule to run periodically, we chose SSIS. The main reason for this is to utilise a for each loop that pulls each tables name from a one column staging table of table names. (that way we can do more or less comparisons by simply adding and removing from the staging table) Also in this loop, using the table name as a variable, we run an exec sql task along the lines of 'SELECT * from varTable EXCEPT SELECT * from varTable_tracker' which gives us the difference beteween the two tables (where the tracker table is a copy of the data table which is sychronised at the end of the job run). So far so good. Now the tricky bit, EXCEPT only works under 2005, the tables are in 2000 so we ended up having a linked server in 2005 back to the 2000 table. Is there a way of acheiving the same result without involving the linked server - or is there a task (script?) we can run to verify the linked server is up before we excecute the job -we already run checks on Connection Managers to see if they are up but never tried linked servers? Lastly, will performance be an issue
Thanks
Hello,
I have been working in ASP.Net web sites with Access database.
Now I want to create a web site using an MS SQL database.
What software or softwares can I use to create and edit MS SQL databases?
I am using Windows XP Professional SP2.
Thank You,
Miguel
Hello,
I have some problems to edit a recordset in an ActiveX DTS using Vbscript.
Here is an example of the script :
dim varsql, varset, varconn
set varconn = CreateObject("ADODB.Connection")
set varset = CreateObject("ADODB.Recordset")
varconn.Open = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=Enregistrement3;user id = 'sa';password=''"
varsql = "SELECT * "
varsql = varsql & "FROM mytable "
varset.Open varsql, varconn, 3,3
msgbox "How much : " & varset.recordcount
if varset.recordcount >0 then
do while varset.eof
varset.edit
.........................
varset.update
varset.movenext
loop
end if
varset.close
varconn.close
Does someone see what is wrong ?
I allways get -1 for the varset.recordcount ! (I checked there are some records into the table).
I don't think it is a problem of user's right as it works with the same user configuration using a SQL action requery.
If I modify the open statement like this :
varset.Open varsql, varconn, 1
varset.recordcount contains the good number of records but the recordset is read noly and can't be modified...
Any Help will be very wellcome !
TIA
anyone know about the undocument options of DBCC?
which options are undomented?
i.e.
log
There are only a few options which are documented in SQL Online Books
thank in advance
This is a basic, remedial question, but I've no experience whatsoever with SQL server or Access (although I'm familiar with basic DB concepts). Even so, I have inherited this project because no one else will take it (besides, I like the challenge of learning something new :-). If I understand correctly, I can use Access 2000 to create and edit DBs on my SQL 7 server, and to run queries. This is done by creating a WBEM ODBC connector (I have no idea how this is done). I'm pretty good at figuring things out on my own, but I sure would appreciate being pointed in the right direction. What I'd like to know is first of all, am I on the right track, and secondly, what do you all recommend as a good source of info on this topic (the Microsoft web site, technet, manuals?).
All info is greatly appreciated.
thanx,
-scott
Is it necessary to reinstall SqlSvr 6.5 in order to chance
from 'per server' to 'per seat' licensing ?
Where do I go to modify the T-SQL statement I entered while creating a job with the web assistant wizard?
View 1 Replies View RelatedSQL 6.5
What are the most critical "dbcc" options that should be run to insure the sanity of database and DBA alike? Do these have to be run in single user mode or can they be run while users are on the system?
Thank You.
I need to convert this from Jet SqL to SQL Server. Moving out of an MDB to an ADP some of the queries I need to change over, I inherited this database and the bosses want all databases moved over from MDB to ADP.
SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));
An automatic monthly delete has recently grown from 15 to 20 million rows. It is now filling my 70GB T-Log completly. I don't have any space to expand the T-Log. Do I have any options other than reducing the number of rows in the delete?
View 2 Replies View Relatedhey guys, i need your help please.
here is the scenario:
1. I need to return a data back to client (result set varies 20-10,000)
2. I only want to show 20 records at a time
3. To get info i need to display i need to join 10 tables
When there are small #s of records it works but when i get over 8000 then it becomes a problem:
1. The first version was:
Get all data using big query and return everything back to client and display only 20 at a time (not very proficient).
Takes around 15 seconds to view 20 records.
2. Inspired by 4GuysFromRolla (http://www.4guysfromrolla.com/webtech/062899-1.shtml)
Use Stored Procedure w/ server side paging logic to get 20 records at the time. I had to pass every filter parameter and stuff. SP had to sort resultset and return only 20 records i need to display.
Takes around 5 seconds to view 20 records.
I still think it's slow, i know this is a very broad question but is there any other way to do it, logically?
Any help is appreciated.
Wojo.
Hi folks, Recently i've installed a fresh Installation of Opertaing Sys and SQL.
Win 2000 server, sp4
SQl 2000 Enterprise, sp3.
I am using Domain user service startup for sql and is member of domain admin. I've manually added the user in Local-Admin group of the system. Obviously it's also has Sys-Admin server roles.
The problem is; when i use enterprise manager and change any of the server settings; Priority Boost or Fix Memory Allocation for the Sql server, nothing happens, the options dialog box closes and doesn't ask for restarting the server neither does the settings take effect when i restart SQL.
However if i change the settings using sp_configure using the same user; it works. i've assigned a fixed memory to SQL but the option "Reserver Physical Memory For SQL" won't work. Couldn't find this option in SP_Configure.
Any ideas, what has gone wrong.
Hi all,
This follows on from a query I had a few days back (and for which I was promptly flamed! However, I've got skin like a rhinoseros, so here goes...)
I have a table - ProjectSite - that is pulling information from a two tables (Project and Site). This table contains data regarding which sites are part of which project.
I now want a means of reporting dates against this. The problem is that each project has bespoke milestone dates, so I can't just create columns in ProjectSite. The only solution I can see is to pull each project (and there's quite a few and its corresponding sites into a new table) and then I can create my bespoke columns.
Does this sound like the best viable option, or can anyone suggest another means of doing this?
TIA,
SamuelT