Relative Paths
Mar 30, 2006
We 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?
View 6 Replies
ADVERTISEMENT
Nov 12, 2007
Hi
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
View 7 Replies
View Related
Apr 16, 2008
I have my SqlDataSource with my connection string:<add name="ConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersLaurensDesktopstockmanASP****.accdb" providerName="System.Data.OleDb"/> but I wanted to make a relative path from it because when I put it online i get an error. Thanks in advance
View 6 Replies
View Related
Jan 7, 2008
using 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.
View 3 Replies
View Related
May 22, 2007
This 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.
View 5 Replies
View Related
May 6, 2007
Hi:
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
View 3 Replies
View Related
May 6, 2007
Hi:
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
View 1 Replies
View Related
Nov 20, 2005
What 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
View Related
Mar 17, 2008
I 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
View 6 Replies
View Related
Jun 28, 2006
I 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
View 3 Replies
View Related
Jan 23, 2007
I have host my ASP.NET application on gate.com, and there I have access to a SQL Server 2005 instance, and I am trying to make relative paths in my web.config work, (|Data Directory|), but I don't know how to change the default value of |Data Directory|, and it isn't the root of my application, I'm pretty sure. My setup is like so, and the file was created using SQL express 2005, I am not sure if that is my problem, because gate uses the full version.
/<root>/App_Data/Leads.mdf
here is the stack trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +766 System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428 System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +410 System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117 System.Configuration.SettingsBase.get_Item(String propertyName) +89 System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36 System.Web.Profile.ProfileBase.get_Item(String propertyName) +68 System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4 ProfileCommon.get_Cart() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot27a74ff4376615bdApp_Code.ceobor1b.4.cs:102 Default2.UpdateTotal() in \shared.hosting.localfscust98045754089webOrder.aspx.cs:45 Default2.Page_Load(Object sender, EventArgs e) in \shared.hosting.localfscust98045754089webOrder.aspx.cs:37 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
View 2 Replies
View Related
Apr 18, 2008
need help
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month
"tb_all_holiday before"
date_start date_end
----------------------------------------------------------
15/03/2008 00:00:00 17/09/2008 00:00:00
20/04/2008 00:00:00 12/05/2008 00:00:00
i must to covert it like this
to this - see relative dates for the next month
"VIEW_all_holiday after -next month only "
date_start date_end
----------------------------------------------------------
01/05/2008 00:00:00 31/05/2008 00:00:00
01/05/2008 00:00:00 12/05/2008 00:00:00
TNX for help
View 2 Replies
View Related
Apr 24, 2007
Hi all,
Is it possible in the XML Source to specify a relative path for the xsd file? If not, do you have any idea why?
Best regards,
pc
View 5 Replies
View Related
Sep 21, 2007
I'm trying to use the drill-through feature to link to a report in different solution, on the same server, but in a different project. Local help files say: "The list of report names includes all reports in the current report server project. If the drillthrough report is on the report server but is not in the project, type the name of the report. The report name can contain a relative or absolute path to the report."
The report will be passing a parameter to the target report through drill-through. The target report is located on different solution and project but on a same server (report manager). Any help will be be appreciated.
Can someone give an example of a relative path? Nothing I've tried has worked, and I've posted this on sqlteam to no avail.
Thanks,
Lalon
View 1 Replies
View Related
Jul 26, 2006
So I want to insert 40 values into a table, starting at a particular column. Like this:
INSERT INTO MyTable (1) VALUES (...my forty values...)
Pretending that 0 (zero) indexes the first column, and 1 indexes the second column, the purpose is to skip the (first) column that contained an identity value (since normally you can't insert into an identity column anyway).
The only way I currently know how to solve this problem, is to use highly verbose syntax, like this:
INSERT INTO MyTable (...my forty column names...) VALUES (...my forty values...)
But yuck, who wants to explicitly mention all forty column names, ONLY BECAUSE I'm trying to avoid inserting a value into the first column which contains the identity?
View 1 Replies
View Related
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
View 14 Replies
View Related
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!
View 4 Replies
View Related
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
View 3 Replies
View Related
Nov 10, 2005
How do I query to ONLY pull the location for the most recent date?
Date Location
1/2/97 ABC
3/4/02 DEF
2/8/89 GHI
4/30/05 JKL
2/28/03 MNO
View 1 Replies
View Related
Feb 28, 2008
At my office, we've been slowly working on putting together a data warehouse.
We're a financial services company and one of the services that we offer is debt collection. As far as reports go, our clients are interested in knowing how much money we collect over time. In particular, they want to know how many payments we've gotten 5, 10, and 15 months (and so on) after we receive a case. (Obviously, the 5-month payments are also included in the 10 and 15-month calculations).
When I wrote this report using our transactional database, I was completely new to SQL and the ever-resourceful Patron Saint took pity on me, so you can see a good description of the details at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78510
Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing, I've been entrusted with researching certain aspects of data warehouse development (rest easy, though, folks - the real DWH work is not being done by the very inexperienced me, but by an actual professional :) ).
My question:
how would you model this kind of "relative time" in a data warehouse? How would you display the 5-month, 10-month, and 15-month payments in a DWH? I can't really imagine that the kinds of joins necessary to do this in a transactional database would be desirable in a data warehouse.
We have the following:
1.) FACT_Payment: A fact table showing each each payment to the most detailed granularity. One attribute of this table is the payment date. Another attribute is a foreign key to the case dimension described below.
2.) DIM_Case: A dimension table showing information on each case, including the case start date. DIM_Case
3.) DIM_Date: A date dimension table.
(For added clarification: The FACT_Payment payment date has to be 5, 10, 15 months etc... after the DIM_Case start date.)
Any ideas, comments, experience with something like this?
Thank you.
View 7 Replies
View Related
Apr 20, 2007
hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.
Is there any property in the SQL Server Agent or mayby a workaround to solve this?
View 7 Replies
View Related
Oct 30, 2007
I'm trying to create a view from a query using a temp table and I'm running into some strange issues. When I try to enter the query into the view it gives me the following error, but then the query runs and returns the expected data:
"Unable to parse query text."
In an attempt to get around this (I assumed that temp tables just weren't supported with in a view) I put my query into a Stored Procedure and attempted to call the SP from the view. This gave me the same results as before (an error and then a result set) but the error was the following:
"The EXECUTE SQL construct or statement is not supported."
The query seems to run properly in both forms, it just gives an error on the first run and won't allow me to save the view because of the syntax error. The stored procedure runs just fine and returns the right data, so I'm pretty sure that my query is good. I just don't know how to make it execute into a view without getting these errors.
Any help would be greatly appreciated.
Thanks!
Phil
View 13 Replies
View Related
Jan 11, 2008
I am new to the wonders of CLR and, as can be expected, have hit a snag. We have multiple environments to run this off of (dev, test, production, etc.) and need to be able to use the same scripts to install them. Here is our basic setup:
Batch script runs a master SQL file using sqlcmd utility
Master SQL file runs secondary SQL files, including the one with the CLR definitions
Secondary SQL file with CLR calls the CREATE ASSEMBSLYI want to be able to call
CREATE ASSEMBLY assemblyName FROM 'myAssembly.dll'
or
CREATE ASSEMBLY assemblyName FROM '..myAssembly.dll'
or something to the effect. However, when I try this, it says I cannot do it or it cannot find the file, even though the file is located in the same folder.
Any ideas?
View 3 Replies
View Related
Sep 11, 2006
Hi 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 Related
Aug 4, 2015
I'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.
View 1 Replies
View Related
Oct 29, 2015
What are the potential risks of keeping data , log , tempdb backups etc in the same drive although in different folders ?
View 4 Replies
View Related
Oct 1, 2003
Here 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.
View 17 Replies
View Related
Jul 20, 2005
Hello,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 Related
Mar 27, 2014
I 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 Related
Feb 26, 2015
I am looking for the script to find the CPU usage for each database like this
Test1DB76.00%
Test2DB12.00%
Test3DB1.21%
Test4DB20.08%
View 4 Replies
View Related
Feb 28, 2015
Have a bit of an SQL challenge in that I need to create a well performing view in SQL2008R2 which calculates hours remaining at any given point in time. This is not a simple sum but has some absolute values included.
---------------------------------------------------------------------------------------------------
-- Basic simplified structure of maintenance and usage tables
---------------------------------------------------------------------------------------------------
CREATE TABLE #Maintenance (Id int NOT NULL
IDENTITY(1, 1)
CONSTRAINT PK_Maintenance PRIMARY KEY,
MaintenanceDate smalldatetime NOT NULL, -- When maintenance hours are reset
HoursAvailable numeric(5, 1) NULL -- Available hours until next maintenance
)
[Code] ....
---------------------------------------------------------------------------------------------------
-- Required output view so that at any given point in time I can determine hours remaining
---------------------------------------------------------------------------------------------------
-- AtDate HoursAvailable
-- '01-Jan-2015 15:00' NULL -- before first maintenance hence unknown
-- '10-Jan-2015 10:00' 10.1 -- maintenance hours reset
-- '11-Jan-2015 07:30' 8.9 -- subtract 1.2
-- '11-Jan-2015 11:10' 2.9 -- subtract 6.0
-- '15-Jan-2015 00:00' -0.1 -- subtract 3.0
-- '01-Feb-2015 00:00' 12.2 -- maintenance hours reset
-- '02-Feb-2015 13:00' 10.0 -- subtract 2.2
View 4 Replies
View Related
Apr 18, 2008
I'm quite a newbie with MDX.
I have a report that uses MDX to return Figures by day. The user selects the Month/Year eg.[Nov F2007] that he wants to see the figures for.
Here is the catch:
The user wants the figures to cut off to the previous Sunday. So if its Thursday, the result set will still only return results to Sunday.
What I'm thinking is something like this:
STRTOMEMBER('[Nov F2007]').FirstChild : STRTOMEMBER('[Nov F2007]').[LastSunday].
I'm not sure if I'm on the right track, but its a mute point if I can't get the previous sunday as a normal day member (ie. 4 November 2007)
Thanks,
Jason
View 3 Replies
View Related
Apr 20, 2006
I am creating a application in Visual C++ 2005 that has database
connections in it. I have gone through the process of setting up the
data source and it will work great on my computer. However, the second
I move either the project or the created executable to another computer
the application keeps crashing. I know that it is crashing due to the
database connection string. The string is setup to work only on my
computer, I am wondering how do I make, or edit, the string to work on
any computer? Thanks for the help.
View 1 Replies
View Related
