SQL 2005 Upgrade Paths
Nov 20, 2005What are the SQL 2005 Upgrade Paths? For example, is there a direct in-place upgrade for SQL 6.5? SQL 7.0? Can someone provide a link please? Thanks in advance.
View 6 Replies
ADVERTISEMENT
SQL 2005 SP1 Upgrade Error For Upgrade Advisor
Jan 19, 2007Hi,
We are performing a SQL 2005 SP1 upgrade from SQL 2000 on our Windows 2003 SP1 Standard Edition.
When we run the upgrade, we got a error "Upgrade Advisor Return -1" as a pop-up window.
When we run the Upgrade Advisor separately, we get this error :
===================================
Common Language Runtime detected an invalid program.
===================================
Common Language Runtime detected an invalid program. (System.Xml)
------------------------------
Program Location:
at System.Xml.Schema.SchemaInfo..ctor()
at System.Xml.Schema.XmlSchemaSet..ctor(XmlNameTable nameTable)
at System.Xml.XmlReaderSettings.get_Schemas()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReportController.LoadAndValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReport.ValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAIssueReport.Refresh()
at Microsoft.SqlServer.UpgradeAdvisor.ReportPanel.OpenReport(String reportFile)
We are in a lost for solutions. We do hope to hear from anyone asap...
Greatly appreciated for any kind assistance.
Thanks.
SQL 2005 Upgrade Problem - In Place Upgrade
Feb 10, 2007Hi,
We are running a test upgrade form sql 2000 standard edition 32 bit to sql 2005 developer edition 32bit. Followed through all the steps and specified the account(SA priveleges and currently used by the 2000 version) and is the local admin on the box for the services and the setup seemed to move fine, except for when it got to the point of installing database services - This is the error message that I got:
MSSQLServer could not be started. Verify you have sufficient priveleges to start system services. The was no choice but to hit cancel and then the set up progressed with a final message of 'SEtup failed'.
Here is a portion of the error log:
Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
Database Mirroring Transport is disabled in the endpoint configuration.
Starting up database 'master'.
Converting database 'master' from version 589 to the current version 611.
Database 'master' running the upgrade step from version 589 to version 590.
Error: 946, Severity: 14, State: 1.
Cannot open database 'master' version 589. Upgrade the database to the latest version.
System Task Aggregate global counters produced an error that was not handled. Major: 9, Minor: 46, Severity:14, State:1
Error: 928, Severity: 20, State: 1.
During upgrade, database raised exception 15151, severity 16, state 1, address 01C4C50B. Use the exception number to determine the cause.
Error: 15151, Severity: 16, State: 1.
Cannot find the endpoint 'TSQL Local Machine', because it does not exist or you do not have permission.
Any help would be greatly appreciated
Thanks KR
The Bane Of Sql 2005 Express... Spending Life Attempting To Upgrade To SQL 2005.
Jan 16, 2008I am out of brain cells. I can not figure out how to upgrade instances of sql express to sql 2005 or even add an instance of sql 2005. I have installed/upgraded everything else (SSMS, etc.) to SQL 2005.
I am dazed and confused. I don't even know where to start in explaining what I have tried and done. I have 303 log files going back to 3/27/2007 from various attempts.
Where do I start in figuring this debacle out???
How To Upgrade From SQLServer Management Studio Express 2005 To SQL Developer 2005
May 25, 2007I currently have SQL Server Management Studio Express 2005 and would like to upgrade my machine to SQL Developer 2005 as easily as possible. Keeping my databases and such.
Any recommendations on the best way to do this would be greatly appreciated.
thanks!
Upgrade From SQL Express 2005 Report Server With Advanced Services To SQL 2005 Standard Fails
Feb 25, 2008I am trying to upgrade SQL Server Express 2005 Report Server to SQL 2005 Standard and it fails with the following error:
SQL Server Setup did not have the administrator permissions required to copy a file: C:Program FilesMicrosoft SQL ServerMSSQL.8Reporting ServicesReportServer
ssrvpolicy.config. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
After I click OK on the message, this whole path where the upgrade is bieng installed dissapears C:Program FilesMicrosoft SQL ServerMSSQL.8Reporting Services. The error is a bit missleading because the file that it is trying to copy rssrvpolicy.config does appear in that upgrade folder so I am not sure why it would try to copy a file that has already been copied.
I was able to upgrade the Database Engine instance fine but can't upgrade Reporting Services due to that error. I have verified that the account that ran the setup had the administrator permissions. Your prompt reply to my question would be greatly appreciatted.
Can I Upgrade From Sql 2005 Express To Sql Server 2005 Enterprise Edition ?
Jul 29, 2006if i upgrade from sql server 2005 expressto sql server 2005 enterprise can i do it ?
am i need to uninstall 2005 express before ?
Upgrade From SQL Server 2005 Express To 2005 Enterprise Fails
Dec 29, 2006Hi
I am trying to upgrade SQL Server 2005 Express to SQL Server 2005 Enterprise Edition.
(As a Preparation to install SQLServer2005 Service Pack 2 (CTP))
I tried to upgrade a particular Instance (OFFICESERVERS, which was based on SQL Server 2005 Express ).
During the upgrade I get a message saying that ...
.....Your upgrade is blocked because of edition upgrade rules.....
All other components I selected for Installation are propertly installed (Analysis, Reporting)
The setup log contains the following detail information:
---- Context -----------------------------------------------
Setting status of unmanaged components and removing unmanaged resources
Error Code: 29543
MSI (s) (DC!C8) [13:57:26:265]: Product: Microsoft SQL Server 2005 -- Error 29543. Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Error 29543. Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
<Func Name='SetCAContext'>
<EndFunc Name='SetCAContext' Return='T' GetLastError='0'>
Doing Action: ValidateUpgrade
PerfTime Start: ValidateUpgrade : Fri Dec 29 13:57:26 2006
<Func Name='ValidateUpgrade'>
<Func Name='updateFeatureSellection'>
Error Code: 0x80077367 (29543)
Windows Error Text: Source File Name: sqlcaupgradeproduct.cpp
Compiler Timestamp: Mon Sep 19 01:07:45 2005
Function Name: sqls::UpgradeProduct::CanUpgrade
Source Line Number: 142
---- Context -----------------------------------------------
I also tried to explicitly upgrade the OFFICESERVERS instance by executing Setup from the command shell:
start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=OFFICESERVERS UPGRADE=SQL_Engine SKUU
PGRADE=1 /qb
The result is the same, i.e.... Your upgrade is blocked because of edition upgrade rules...
Does anybody know about this problem?
Some more information about the system:
W2K3 R2 Enterprise Edition
WSS3.0
MOSS2007
previously installed SQL Server 2005 Express Edition (Version 9.00.2047.00)
Kinde regards
Andreas
SQL Installation Paths
Nov 12, 2007Hi
I have sql 2000/2005 installation path errors in some of prodution servers. Like we have standards that backup files should go to E: drive and data files to f: and log files to h:. Can any one help me in this issue what can be done with out reinstallation,
Thanks in advance
Relative Paths
Mar 30, 2006We have a growing issue where we have a relative dtsconfig file (which stores the absolute base path of the ETL packages). This way we can keep the ETL projects failry portable - only having to modify one value in the dtsconfig file. The master package that defines the dtsconfig location (which is config/Default.dtsconfig) usually interpretates this location to be relative the project. The problem is that every now and again when you open this package in .NETStudio, the path is interpreted differently and causes: config/Default.dtsconfig to state invalid path. But when we delete the variable (which defines the dtsconfig path), save/close and open/recreate it works again. This may or may not be supported MS method, but I was curious to know why this gets messed up. Is there somehwere in the .NET framework that defines what "/" is relatively under?
For example: Our absolute config path is "D:Program FilesMicrosoft SQL Server90DTSPackagesETLProjectETLBaseconfigDefault.dtsconfig" but using: "config/Default.dtsconfig" for xml file value works. However, sometimes we will get an error stating that this file cannot be found, and when we just try to delete (without saving and closing) and immediatelly try to put "config/Default.dtsconfig" again and hit next, we get an error and the path is now:
'D:Program FilesMicrosoft SQL Server90DTSPackagesDEVDataExchangeETLBaseconfigconfigDefault.dtsconfig'.
Ideas?
Expired SQL 2005 Evaluation Upgrade To SQL 2005 Standard
Mar 12, 2008I've been reading and looking over what to do for my situation for several days. I need to ask and see if I can get a clear answer.
My Scenario:
I have a SQL Server 2005 Eval installed that is currently running with live data. I need to upgrade that to a recently-purchased full version. I have several databases set up. I have ready the article 914158 that states it will fail. It's still running so I can access everything. If I restart or stop the service I'm screwed.
That do I do?
Thanks so much!
Can We Upgrade From Express 2005 To Standard/Workgroup 2005
Aug 8, 2006Can we upgrade from Express 2005 to Standard/Workgroup 2005??If possible with what complexities can it be done??
View 1 Replies View RelatedNot All Code Paths Return A Value
Jan 7, 2008using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{ [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="Obj_Row",
IsDeterministic=true,IsPrecise=true,
TableDefinition="ObjID int,OjbDataID int,ObjDataValue nvarchar(400)",DataAccess= DataAccessKind.Read)]public static IEnumerable Obj_IDs(SqlInt32 Data_1, SqlInt32 Data_2, SqlInt32 Data_3)
{using (SqlConnection conn = new SqlConnection("context connection=true"))
{
try
{ if (!Data_2.IsNull)
{string sql = @"Select Obj_ID, Obj_Data_ID, Obj_Data_Value from tbl_Obj_2";
SqlCommand cmd = new SqlCommand(sql, conn);SqlDataAdapter da = new SqlDataAdapter(cmd);DataTable dt = new DataTable();
da.Fill(dt);
conn.Open();return dt.Rows;
}if (!Data_3.IsNull)
{string sql = @"Select Obj_ID, Obj_Data_ID, Obj_Data_Value from tbl_Obj_3";
SqlCommand cmd = new SqlCommand(sql, conn);SqlDataAdapter da = new SqlDataAdapter(cmd);DataTable dt = new DataTable();
da.Fill(dt);
conn.Open();return dt.Rows;
}
}catch (Exception ex)
{
ex.Message.ToString();
}
finally
{
conn.Close();
}
}
}public static void Obj_Row(Object item, out int ObjID, out int ObjDataID, out string ObjDataValue)
{DataRow row = (DataRow)item;
ObjID = Convert.ToInt32(row["Obj_ID"]);ObjDataID = Convert.ToInt32(row["Obj_Data_ID"]);ObjDataValue = row["Obj_Data_Value"].ToString();
}
};
//Error 1 'UserDefinedFunctions.Obj_IDs(System.Data.SqlTypes.SqlInt32, System.Data.SqlTypes.SqlInt32, System.Data.SqlTypes.SqlInt32)': not all code paths return a value
I'm newbie. Please, show me how to correct the problem. Thank you.
Variable Paths For Different Environments
May 22, 2007This has probably been covered in other posts. I have been working with SSIS for the past month and I am trying to follow best practices on various items. Having worked with a different ETL tool prior to this, I am wondering what is the best approach to use for Connections and File Paths.
What I would normally do with DataStage for this would be to assign a Job Variable (and eventually Sequence Variable) of the type: Path. So, if I was developing a job I would create SourceFilePath, ErrorFilePath, etc. I would use these variables in a FlatFile or Dataset Stage. For instance I would assign a filename for a source as: #SourceFilePath#SourceFile1.txt. During execution the job would load the variable and then the filename would be: C:MyDocumentsDatafilesSourceFile1.txt.
When its time to move to another environment, I don't have to worry about changing values for file connections because it is managed dynamically by a config file or whatever method.
What is the best practice that emulates this behaviour for SSIS? I've been thick and can't get my head around this. Any direction to blogs or user sites would be great. Examples, even better!
Thanks in advance.
Table Paths And .udl Files
May 6, 2007Hi:
I am trying to set up a TEST ENIVORNMENT for a reservation software package. I need this setup so that I can run various scheduling scenarios in order to optimize operations. Below I have been given instructions from the software vendor on how to set up my SQL database. However, I am a little confused on what to do for a couple of the steps. They are as follows:
***In SQL server 2000 enterprise manager on the test laptop, change the 2 path settings in the table SGCONFIG. The should be changed to C:StratagenAdept5Server. ** YOU WILL NEED TO DO THIS STEP EVERYTIME YOU RESTORE A BACKUP ADEPT5_CLASTRAN.BAK FROM THE PRODUCTION TO THE TEST ENVIRONMENT
QUESTION: How do you change the path settings on a table?
***Open the .udl files in the apps folder and the server apps folder and check to see that they are pointing to the test laptop server, not the production server.
QUESTION: What are the .udl files and how do you check to see if they are pointing at the test laptop server only?
Thanks sincerely for your help. I am trying to meet a deadline for a meeting tomorrow. Therefore, I am desperate. Please send me email. rtanner@clastran.com.
Ron
Table Paths And .udl Files
May 6, 2007Hi:
I am trying to set up a TEST ENIVORNMENT for a reservation software package. I need this setup so that I can run various scheduling scenarios in order to optimize operations. Below I have been given instructions from the software vendor on how to set up my SQL database. However, I am a little confused on what to do for a couple of the steps. They are as follows:
***In SQL server 2000 enterprise manager on the test laptop, change the 2 path settings in the table SGCONFIG. The should be changed to C:StratagenAdept5Server. ** YOU WILL NEED TO DO THIS STEP EVERYTIME YOU RESTORE A BACKUP ADEPT5_CLASTRAN.BAK FROM THE PRODUCTION TO THE TEST ENVIRONMENT
QUESTION: How do you change the path settings on a table?
***Open the .udl files in the apps folder and the server apps folder and check to see that they are pointing to the test laptop server, not the production server.
QUESTION: What are the .udl files and how do you check to see if they are pointing at the test laptop server only?
Thanks sincerely for your help. I am trying to meet a deadline for a meeting tomorrow. Therefore, I am desperate. Please send me email. rtanner@clastran.com.
Ron
Need To Change Hardcoded Paths
Mar 17, 2008I have created over a hundred reports and each of them are scheduled to output a pdf. The problem is this was temporary and each week I would like to output the reports to a directory that is based upon time. For example, I have 13 groups of reports and each group has 9 reports. Each group has it's own directory and folder. IE group A would be saved in something like \client filesgroup aweekly reportsMar 17 2008. The next week the reports for the same group need to be saved in \client filesgroup aweekly reportsMar 24 2008. This would be the same for each of the groups; group A, group B etc.
The question is there a way to set these paths dynamically or at least iterate through the subscriptions and change the paths?
Cheers
How To Choose One Of Two Success-paths?
Jun 28, 2006I have an ActiveX Script Task in SQL Server 2000. It chooses one of two
possible success-paths depending on if a file exist or not. (Part of the old ActiveX Script for choosing next step is below)
I need to rewrite this for a Script Task in SQL Server 2005 but it seems like it
doesn't have this functionality and objects.
Does anyone know how to write the code for choosing the next step in a
Script Task or knows another way to solve my problem?
The package is not suppose to fail if the file is missing, it's must succeed and that's why I need two success-paths.
Regards,
Sara
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main ()
Dim pkg
Dim stpContinuePkg
Dim stpExit
SET pkg = DTSGlobalVariables.Parent
SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_4")
SET stpExit = pkg.Steps("DTSStep_DTSActiveScriptTask_21")
If ...... Then
Main = DTSStepScriptResult_ExecuteTask
stpContinuePkg.DisableStep = False
stpExit.DisableStep = True
Else
.......
End If
Main = DTSTaskExecResult_Success
End Function
Upgrade SQL 2005 Standard To SQL 2005 Enterprise
Jun 8, 2006Hi
we have installed SQL 2005 Standard and we are planning to upgrade to SQL 2005 Enterprise version. Do i need to uninstall SQL 2005 Standard and reinstall Enterprise or i can install SQL 2005 Enterprise on top of standard version to upgrade.
Thanks a lot in advance
Upgrade SQL 2005 Standard To SQL 2005 Enterprise
Nov 1, 2007Can SQL 2005 32bit Standard edition be upgraded to SQL 2005 32bit Enterprise Edition in-place?
View 4 Replies View RelatedUpgrade SQL 2005 Standard To SQL 2005 Enterprise
Nov 1, 2007Can SQL 2005 32bit Standard edition be upgraded to SQL 2005 32bit Enterprise Edition in-place.
View 12 Replies View RelatedReplacing Paths In A Filename Collumn
Jan 16, 2004[[Disclaimer: I am by no means a highly-skilled DBA. I have just enough db skills to get my job done, which involves light sql server and oracle tasks. I appreciate the help, guys.]]
I am trying to replace the path section of filenames listed in a collumn of my database. The filenames are UNC paths, and all files have 8 digit filenames with the same extension (they are all tif images). The length of the paths in the filenames will change, however.
Here is what my old paths look like:
\imageimagesNYY-MMABCDEFGH.TIF
..and here is the new path (all of the images are being consolidated into one directory):
\als-imagealscom31imagesdbABCDEFGH.TIF
-------------------------------------------------------------
I have tried using the following query to do this:
update image set filename=replace(filename,'\imageimages5 0-08','\als-imagealscom31imagesdb')
This returns insanely crazy results like 460239 row affected, when it should be about a thousand.
--------------------------------------------
I have also tried the following:
update image set filename=right(filename,12) + '\als-imagealscom31imagesdb' where left(filename,21) = '\imageimages5 0-08'
This affects zero rows.
Please guide me down the one true path. My coworkers are clueless and I'm quickly running out of disk on the old box.
peace,
-jake
How To Set SQL Server And VS Default Folder Paths?
Jul 24, 2007
Ive just installed VS2005 Pro and SQL Server Developer edition.
Now I want to set the default paths for project, templates, settings. But I want for both VS and SQL files.
For example, right now the default folders for SQL Studio are:
.My DocumentsSQL Server Management StudioProjects
.My DocumentsSQL Server Management StudioSettings
.My DocumentsSQL Server Management StudioTemplates
.My DocumentsSQL Server Management StudioBackup Files
... and so on.
For VS Studio:
.My DocumentsVisual Studio 2005Projects
.My DocumentsVisual Studio 2005Templates
.My DocumentsVisual Studio 2005Code Snippets
... and so on.
I would like for all sub-folders(from SQL Server and VS) to be in one same folder(Development), for example:
.My DocumentsDevelopmentProjects
.My DocumentsDevelopmentSettings
.My DocumentsDevelopmentTemplates
.My DocumentsDevelopmentBackup Files
.My DocumentsDevelopmentCode Snippets
... and so on.
I have tried changing in each application´s options the default path for each one, pointing to each folder I listed above.
My problem is that both VS and SQL Server still keeps creating folders in the old locations, for example: ".My DocumentsSQL Server Management StudioProjects". This example happens when I create a new query, and try to save it, it automatically creates that folder.
It only works for a few of them, like the settings folder. Ive managed to create a single folder for SQL and VS setting files.
Is there a way I can join both Application folders? I want to keep both projects files in one folder, both setting files in one folder, and so on...
I hope I explained well my situation.
Thanks!
How To Set Permissions For Accessing Different Server Paths
Apr 10, 2008
Currently I have an sql string which looks like this
INSERT INTO tblPDFFiles (fileType,PDFcontent) SELECT 'First test file', BulkColumn FROM OPENROWSET(Bulk 'C://Test.pdf, SINGLE_BLOB) AS BLOB
But the files i am trying to access is on a different shared server called 'test2008' now im told I can access this doing
INSERT INTO tblPDFFiles (fileType,PDFcontent) SELECT 'First test file', BulkColumn FROM OPENROWSET(Bulk '
\test2008 estpdf.pdf', SINGLE_BLOB) AS BLOB
Which I made sure had the pdf there I get the following error
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "INSERT INTO tblPDFFiles (fileType,PDFcontent) SELECT 'First test file', BulkColumn FROM OPENROWSET(Bulk '\test2008 est.pdf', SINGLE_BLOB) AS BLOB" failed with the following error: "Cannot bulk load because the file "\test2008 est.pdf" could not be opened. Operating system error code 5(Access is denied.).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at Foreach Loop Container: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
How do I give access to this other server to my SSIS package
Cycles Or Multiple Cascade Paths Error
Sep 11, 2006Hi there.I've been searching for this error specifically but I haven't found anything yet.I have these two tables (USERS and REQUESTS):USERS ( [LOGIN] [varchar] (10) NOT NULL , [NAME] [varchar] (20) NOT NULL)where LOGIN is the primary key.The problem comes when I try to create the "REQUESTS" table.In these requests there's one user who types the request. After one or two days, there's other user who aproves the request. The problem is that I need two foreign keys referencing the table "USERS".CREATE TABLE REQUESTS ([ID] [numeric](5, 0) NOT NULL ,[DATE] [datetime] NOT NULL ,[NOTES] [varchar] (100) NOT NULL ,[TYPED_BY] [varchar] (10) NOT NULL ,[APROVED BY] [varchar] (10) NULL) ON [PRIMARY]GOALTER TABLE REQUESTS ADD CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY ( [ID]) ON [PRIMARY] GOALTER TABLE REQUESTS ADD CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY ([TYPED_BY]) REFERENCES [USERS] ([LOGIN]) ON UPDATE CASCADE ,CONSTRAINT [FK__REQUESTS__APR__12742E08] FOREIGN KEY ([APROVED_BY]) REFERENCES [USERS] ([LOGIN]) ON UPDATE CASCADEAnd SQL returns:Introducing FOREIGN KEY constraint 'FK__REQUESTS__APR__12742E08' on table 'REQUESTS' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Could not create constraint. See previous errors.Ok, after that, I tried creating a new table to store aprovals (Table with two fields: "REQUEST_ID" and "APROVED_BY").So, I removed "APROVED_BY" field from "REQUESTS" and its FK constraint.The same error comes up.I don't think this structure goes into "cycles" or "multiple cascades".How can I do this?Thanks in advanceRegardsRoland
View 3 Replies View RelatedSetting Up Directories - Data Paths In Server?
Aug 4, 2015I'm curious if there's a "best practice" for setting up the data directories MS SQL will use for each operation? I've allocated independent disks for things like C: (OS), E: (DATA), etc etc etc but I'm not familiar w/ MS SQL to understand how DBA's commonly configure the folders under each unique disk for things like DATA, LOGS, BACKUP, INDEXES, and TEMPDB. Should I have an identically name folder as show below in my example?
You can see I've just mirrored the drive name to a new folder under the partition so data is being written to: F:DATA and E:LOGS. Is this considered correct / good practice? I assume naming the folder in each mount point to whatever I logically called the drive is correct but if I should change how I configure my drive paths above. I'm trying to learn common good SQL Server practices and while I work on properly installing SQL Server 2012/2014, I want to make sure I configure my partition names SQL will utilize correctly.
SQL Server 2008 :: Paths Not Well Defined For Databases
Oct 29, 2015What are the potential risks of keeping data , log , tempdb backups etc in the same drive although in different folders ?
View 4 Replies View RelatedFinding All Minimal Cyclic Paths In The Graph
Oct 1, 2003Here a code for finding all minimal loops (cyclic paths) in a graph
with vertexes of degree >= 3. Almost obviously that before seeking
for loops we should eliminate from the graph all its vertexes of degree < 3
(degree of a vertex is the number of edges outcoming from the vertex).
Note: there are no any 'parent' - 'child' nodes here. All vertexes are
absolutely equitable.
if object_id('g3')>0 drop table g3
if object_id('g3x')>0 drop table g3x
if object_id('g3y')>0 drop table g3y
if object_id('g3l')>0 drop table g3l
GO
create table g3y(v1 int, v2 int) -- ancillary table
GO
create table g3x(n int, v1 int, v2 int) -- ancillary table
GO
create table g3l(nl int, v1 int, v2 int)
-- table for storing of 'detected' loops
GO
create table g3(v1 int, v2 int)
-- table of test data with pairs of adjoining vertexes
-- each vertex is named by an arbitrary number
GO
insert into g3
select 2, 3 union all
select 2, 4 union all
select 1, 4 union all
select 3, 5 union all
select 5, 6 union all
select 1, 6 union all
select 4, 7 union all
select 6, 8 union all
select 3, 9 union all
select 1, 7 union all
select 2, 7 union all
select 1, 8 union all
select 5, 8 union all
select 2, 9 union all
select 5, 9 ----union all
/*
select 2, 13 union all
select 3, 13 union all
select 13, 14 union all
select 12, 14 union all
select 12, 15 union all
select 11, 15 union all
select 11, 13 union all
select 10, 11 union all
select 10, 12 union all
select 10, 14 union all
select 10, 15
*/
GO
insert into g3 select v2, v1 from g3
declare @i int, @n int, @v1 int, @v2 int
set @i=1
while 0=0
begin
set @n=1
truncate table g3x truncate table g3y
select top 1 @v1=g3.v1, @v2=g3.v2 from g3 left join g3l on
(g3.v1=g3l.v1 and g3.v2=g3l.v2)or(g3.v1=g3l.v2 and g3.v2=g3l.v1)
where g3l.nl is null if @@rowcount=0 break
insert into g3x select @n, @v1, @v2
while @v1<>(select top 1 v2 from g3x order by n desc)
begin
set @n=@n+1
insert into g3x select top 1 @n, v1, v2 from g3 where v2=@v1
and v1<>@v2 and v1=(select top 1 v2 from g3x order by n desc)
if @@rowcount=0
begin
insert into g3x select top 1 @n, v1, v2 from g3 where
v2 not in (select v1 from g3x union all select v2 from g3x) and
v1=(select top 1 v2 from g3x order by n desc) and not exists
(select 0 from g3y where g3y.v1=g3.v1 and g3y.v2=g3.v2)
if @@rowcount=0
if @n>2
begin
insert into g3y select v1, v2 from g3x where n=@n-1
delete from g3x where n=@n-1
set @n=@n-2
end
else
begin insert into g3l select 0, v1, v2 from g3x break end
end
else
begin
insert into g3l select @i, v1, v2 from g3x set @i=@i+1
end
end
end
select * from g3l order by nl
Below is what we get:
nl v1 v2
----------- ----------- -----------
1 2 3
1 3 5
1 5 6
1 6 8
1 8 1
1 1 4
1 4 2
2 1 6
2 6 8
2 8 1
3 4 7
3 7 1
3 1 4
4 3 9
4 9 2
4 2 3
5 2 7
5 7 4
5 4 2
6 5 8
6 8 6
6 6 5
7 5 9
7 9 3
7 3 5
Of course, in general case not all found by the code loops are minimal.
But this is exactly my approach:
firstly find any possible loops (avoiding excessiveness!!),
then, in WHILE loop, try to mark out minimal loop(s) from intersection of
two non-minimal loops... seems it will be an interesting t-sql job.
Seems Unavoidable Multiple Cascade Paths. How To Avoid?
Jul 20, 2005Hello,There are three tables:OS-GroupOFCompanies (Table1)GoC_GroupOFCompaniesID (PK)OS-Organization (Table 2)Org_OrganizationID (PK)OS-UnitAddress (Table 3)Unit_UnitAddress (PK)Scenario:(1)GoC_GroupOfCompanies has -one to many- relationship with Org_OrganizationID.(2)GoC_GroupOfCompanies has -one to many- relationship with Unit_UnitAddress.(3)Org_OrganizationID has -one to many- relationship with Unit_UnitAddress.Following Error message appeared after trying to save the relationship (3) described above.'OS-Unit-UnitAddress' table saved successfully'OS-Organization' table- Unable to create relationship 'FK_OS-Organization_OS-Unit-UnitAddress'.ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_OS-Organization_OS-Unit-UnitAddress' on table 'OS-Organization' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.NB: ON UPDATE is much needed.How to manage the situation?Please guide.ThanksSuryaPrakash****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...f935efcabb55ee9*****************************************
View 3 Replies View RelatedTransact SQL :: How To Calculate Size Of Files That Have UNC Paths For
Mar 27, 2014I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
View 4 Replies View RelatedMultiple Execution Paths In SSIS Control Flow
Apr 18, 2007Hello all,
Is there documentation somewhere about multiple execution paths in SSIS control flow? I didn't find documentation anywhere. I have a situation where I have two tasks that take considerable time, but could be executed in parallel (to speed up things) and I was wondering whether SSIS supports parallelism.
To illustrate the issues in simultaneous execution, I created a test SSIS package. In the package, I have five tasks, let's call them T1, T2, T3, T4 and T5. The taks are connected with "green arrows" like this:
T1->T2
T1->T3
T2->T4
T3->T4
T5 is not connected. The tasks can be e.g. Send Mail tasks, that's not relevant to this issue. I put a breakpoint in each task and execute the package.
When I execute package, T1 and T5 become active, i.e. the arrow that displays where the package execution currently is, is in two tasks simultaneously. Now F10 (step over) doesn't seem to work "Unable to step. Not implemented". If I press F5 nothing happens. After I press F5 for a second time tasks T1 and T5 and executed. Why don't they execute with the first pressing of F5? I would additionally like to know whether these two tasks are executed in parallel or sequentially, i.e. in the same thread or in two threads? Is there documentation of this?
The execution stops at T2&T3. Again, pressing F5 doesn't do anything, but the second time I press F5 T2 and T3 are executed.
Transact SQL :: Make Tracks Or Paths Of Parent Has Many Child
Nov 19, 2015I have a  tblActivity table :
ActivityID ActivityName
-----------------------------
1 A1
2 A2
3 A3
4 A4
5 A5
6 A6
7 A7
8 A8
9 A9
10 A10
and related it in tblParentActivity table with parent child relationship as:
ParentActivityID ChildActivityID
-------------------------------------
1 2
1 3
2 4
2 5
3 6
4 7
5 7
6 8
7 9
8 9
9 10
I want to write a SQL query which will make it in tracks depend on how many childActivity in one parentActivity like
ActivityID TrackGroup
-------------------------
1 1
2 1
4 1
7 1
9 1
10 1
[code]....
May Cause Cycles Or Multiple Cascade Paths Error With Only Two Tables
Apr 11, 2008Hi,
I have two tables called a and b. a has one row called aId where aId is the PK. b has bId, aId_1 and aId_2 where bId is the PK and aId_1 and aId_2 both allow nulls.
If I have a relationship between aId and aId_1, and another relationship between aId and aId_2, where I set the delete rule for both to SET_NULL, then I get the error -
"may cause cycles or multiple cascade paths"
But its completely reasonable that I might wish to do this.
The funny thing is that under Visual Studio, I can create a Data Set with these tables and using the designer, I can set the both foreign key relationships to Delete Rule Set Null and everything works as expected.
So, I'm unsure now if I just need to set the Delete rules using the Dataset designer and not bother with them in the database itself.
Any comments?
Thanks,
Barry.