- An Exception Occurred While Executing A Transact Sql Statement Or Batch Restoring Database
- Index Space An Exception Occurred While Executing A Transact Sql Statement Or Batch
- An Exception Occurred While Executing A Transact-sql Statement Or Batch 2012
I have recently installed SQL Server 2012 on my machine. When I try to create a database in SSMS by right clicking on
Databases
and selecting New Database
, it prompts me for various items in order to create the database. After entering the name of the database and clicking OK, I get an exception:An Exception Occurred While Executing A Transact Sql Statement Or Batch Restoring Database
'Create failed for Database 'aaaa'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
A file activation error occurred. The physical file name 'aaaa.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5105)'
It seems the problem is only with the wizard because when I execute
marc_sCreate Database
query it successfully creates the database.595k135135 gold badges11421142 silver badges12801280 bronze badges
WasimWasim
Index Space An Exception Occurred While Executing A Transact Sql Statement Or Batch
3 Answers
I figured it out that when Database is created from wizard, a file path is to be provided in Path column. If it is blank by default then it means there is no path specified in Database settings.
In Object Explorer, right-click a server and click Properties.In the left panel, click the Database settings page.In Database default locations, view the current default locations for new data files and new log files. To change a default location, enter a new default path name in the Data or Log field, or click the browse button to find and select a path name.
We can change the file path while creating Database.
WasimWasim
The actual database file permissions were set to read_only,please try unchecked the read_only checkbox on the file permissions.
yuezheng.huyuezheng.hu
I also had this problem and I can in this link where I've created any string values (DefaultData and DefaultLog) in regedit in this path: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer.In this 'string values' you must put the path where your SQL data and log must stay.
Paulo FernandoPaulo Fernando
Not the answer you're looking for? Browse other questions tagged c#sqlsql-serverdatabase or ask your own question.
I have loaded SQL 2005 on an SBS server. I'm trying to set up a maintenance task to automatically back up a database called Tourplan to a second hard drive nightly at 10 p.m. Although the backup job itself creates okay, when I try to schedule it I get an error and it will not schedule. If I run the maintenance plan Wizard to do exactly the same thing, I get an error at the end of the Wizard shown below.TITLE: Maintenance Plan Wizard Progress
------------------------------
Create maintenance plan failed.
------------------------------
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
An Exception Occurred While Executing A Transact-sql Statement Or Batch 2012
------------------------------
The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Suggestions as to what I need to do to overcome the scheduling problem would be appreciated. I am pretty green on SQL so pointing it to step-by-step processes is best.
Please explain what is required to set up a SQL Server linked server. Age of empires 3 expansions download.
Server A is SQL 2005 windows logins onlyServer B is the same (SQL 2005 windows logins only)
Server A runs windows XPServer B runs Windows Server 2003
Both SQL Server services are running under the same domain account. I am logged into my workstation with a domain account that has administrative rights on both SQL Servers.
Note these are both SQL Server 2005 SP2 - I've had old hotfixes pointed out to me, but those are already applied.
The issue I am having is this error: 'Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)'
MDMarra93.3k2828 gold badges177177 silver badges314314 bronze badges
ScottStonehouseScottStonehouse57944 gold badges1414 silver badges2424 bronze badges
migrated from stackoverflow.comNov 28 '09 at 4:15
This question came from our site for professional and enthusiast programmers.
9 Answers
Windows 10 unactivated vs activated key. From My understanding of this issue it's a 'HOP' issue.
i.e. you are trying to use server A to relay your login details (with SSPI) to Server B.
In SQL Server 2005 they have added a whole load of security issues that make this harder than it should be. The words 'Kerberos Authentication' will become the bain of most sys-admins/DBA's lives. It effectively is used for pass-through authentication.
Here are the basics of what you need. 1) The servers (A and B) need to be set-up in Active Directory(AD) with delegation for Kerberos enabled. (this is set through your active directory admin panel)
2) The service account that your SQL Servers run under need to have delegation enabled also (this is also set through your active directory admin panel). - if they are not running under a service account, you need to create one.
3) The Servers need to have SPN's defined for the instance and the HOST and the machine name. (Using a tool called SetSPN in the windows support tools)
Support Tools (SetSPN is in this set) http://www.microsoft.com/downloads/details.aspx?FamilyID=96a35011-fd83-419d-939b-9a772ea2df90&DisplayLang=en
(Overview of how to add an SPN)http://technet.microsoft.com/en-us/library/bb735885.aspx
4) You may need to set your DB to 'trustworthy'
ALTER DATABASE SET trustworthy on
5) After you have all of this done restart your instances.
6) Then try create your linked server again.
Finally you can test your connection to SQL Server.This should work fine if you have it all configured correctly.
This will tell you your connection authentication type.
You want to get 'KERBEROS' here and not 'NTLM'.
It's a slippy slope, KERBEROS and Pass-through delegation, stick with it and you will eventually figure it out.
ReferencesKerberoshttp://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
Other manifestations of the problemhttp://www.sqlservercentral.com/Forums/Topic460425-359-1.aspx
I hope this all helps.
evilhomerevilhomer
You can also use SQL Server Management Studio (SSMS) to manage create linked servers as well if you're more comfortable with the GUI. To do so:
- Launch SSMS and connect to one of the instances of SQL Server you want to link
- Expand 'Server Objects' in Object Explorer
- Right click 'Linked Servers' and choose 'New Linked Server'
- On the 'New Linked Server' dialog, select 'SQL Server' as the Server Type and enter the instance of SQL Server you'd like to link to.
- On the 'Security' page, select how users will authenticate from the current server to the linked server. You mentioned both servers are set up to use Windows Logins. If this is the case, under the section labeled 'For a login not defined in the list above, connections will:' I would probably choose the option labeled 'Be made using the Login's current security context'.
Note that this assumes that users who have logins on server A also have logins on server B.
Tim LentineTim Lentine
I'm going nuts with the same problem! I remember doing this with 2000 was always easy. I have been all over google and I can't get this to work. Exact same setup, both servers running on a domain account, Windows auth.
I'm trying to use named pipes instead of TCP and at least I get a different error:
This might have something to do with enabling names pipes, but I can connect via sqlcmd from server A to server B like this:
If I don't used named pipes, and just do:
I get this:
[Edit] I started a discussion on Sql Server Central about this. Basically, you have to do some complicated configuration related to Kerberos delegation to get this to work.
I decided to just create a single, limited Sql Login account to handle the linked queries. I hate resorting to that, but it seems more secure than the changes you have to make to get it working with windows auth.
Eric Z BeardEric Z Beard35311 gold badge55 silver badges1212 bronze badges
If you do a search on sp_addlinkedserver and sp_linkedservers, you get some examples. It is pretty straightforward to setup.
Tim CochranTim Cochran
Also if you have SQL Manager, you can add with it's GUI.
Basically you need to link the two servers either by the SPs mentioned by Tim, or via GUI and then set the access rules (which is not even needed if you use Windows authentication on both servers).
Biri
I know this is supposed to be easy, but it's not working for me at all - I'm having security issues here. So I would like someone to spell out the steps for me.
I've done this in the past on SQL 2000 with no issues.
ScottStonehouseScottStonehouse57944 gold badges1414 silver badges2424 bronze badges
So you can link them, but cannot execute a query because of wrong accounts?
Does the windows user you try to use has rights to read data on both server?
Fs2004 a340-600 sounds. Once I also had a problem because the 'data access' property was set to false for some unknown reason.
Also try out what happens if you explicit set one user to another user for the link.
(These all can be done in SQL Manager.)
Biri
Tim has posted the exact steps that I had assumed were the correct ones. Step 5 is the security page. I select 'Be made using the login's current security context'.
When I click ok, I get the following error. I don't know why it's trying to use 'NT AuthorityAnonymous login'. I'm logged into my workstation with my domain account which has all rights on both servers.
TITLE: Microsoft SQL Server Management Studio
'The linked server has been created but failed a connection test. Do you want to keep the linked server?'
------------------------------ ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3068&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
ScottStonehouseScottStonehouse57944 gold badges1414 silver badges2424 bronze badges
Try to do this while you are logged in locally to the server, if you do it from a remote machine you might not be sending the proper credentials.
user59243