I am trying to understand constraints and minimum cardinality.
In a relationship between 2 tables, t1 and t2, with a parent to child relationship of
1 to 1 or more
how is the minimum cardinality enforced?
If both sides of the relationship require at least one occurrence, how would the insert be done? How can you insert into table t1 when it has a constraint that there must be an occurrence in table t2, or vice versa?
Any help in understanding this is greatly appreciated!
Are there any built-ins, utilities, ... within sql server 2000 that can be used to enforce password complexity in the database. i.e at least 8 characteres, upper and lowercase mix, includes at least on number and one punctuation (underscore,...).
I understand that this requirement can be achieved using windows authentication. Unfortunately we do not have that luxury (at the moment).
Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ======================================== CREATE TABLE ======================================== */ CREATE TABLE [dbo].[PersonInfoSmall]( [PersonID] [BIGINT] NOT NULL, [FirstName] [NVARCHAR](50) NOT NULL, [MiddleName] [NVARCHAR](50) NULL, [LastName] [NVARCHAR](50) NOT NULL,
-----Cross posted on comp.databases.ms-sqlserver andmicrosoft.public.sqlserver.programming-----Can someone please explain cardinality to me? I am having a problem with aninsert from a webpage.When a user adds a venue with an event I need both tables to populate.Currently only the event table populates the right info and the venue doesnot. I am using SELECT SCOPE IDENTITY to get the last inserted venue toassociate with each event. That is to say that each event has a column forvenue and that value is incorrectly being added as 0, the default value. Thevenue is not getting added.It worked fine until I got to ID number 87 which was the 62nd venue.When I look at my table structure in php myadmin my cardinality is differentdepending on what field. ID is 67 and Address is 6. Something seems wierd,but I don't understand cardinality so much.Thanks!
update #table1 set firstCol = firstCol +( select secondCol from #table2 where #table1.id = #table2.id )
return error message :
Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
2. shape UPDATE
update r set firstCol = firstCol + s.secondCol from #table1 r join ( select id,secondCol from #table2 ) s on r.id = s.id
return message : (1 row(s) affected) and #table1 is updated only one of row ( generally random ) from #table2 for ID = 1
select * from #table1
id firstCol ----------- ----------- 1 10 2 0
3. shape UPDATE
update #table1 set firstCol = firstCol + secondCol from #table2 where #table1.id = #table2.id
return message : (1 row(s) affected) and #table1 is updated only one of row ( generally random ) from #table2 for ID = 1
Hi I am using the databse diagrammer to create a database. creating the tables and fields is no problem but there seems to be a lack of choice with joins and I cannot see how to set cardinality. any help appreciated.
DELETE FROM [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]
gives this error
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'SCS_NAV2009R2_PROD' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
~ 7,000,000 records
now i have set the log to autogrow and set the max size to 2tb
Hello all. I'm running SQL Server 2000 and I'm trying to get a veryfew, recent rows of data from a table based on an indexed datetimecolumn. Here's my predicate:where order_date > dateadd(hour, -1, getdate())i.e. everything more recent than one hour ago. This corresponds to the3 or 4 rows in which I'm interested. I have order_date indexed and Ihave current statistics. When I check the explain plan for this queryI see expected rows returned: 114,000. When I go on to join to severalother tables I end up with unnecessary hash joins -- due to theinaccurate cardinality estimates on this table.However, if I use the following predicate (which corresponds to datawithin the last 3 days):where order_date > '2006-03-24'then I see an estimated rows returned: 6 -- which is pretty accurate.[color=blue]>From there the rest of the query's explain plan falls together nicely.[/color]So my question is: how do I get the optimizer to realize that one hourago is pretty recent?Many thanks,Scott
I'd like to create a relationship between two tables in sql server 2005, where a User can be a Partner and a Partner must be a User. So it 0-1 and 1-1. How do I enforce this relationship?
I will also be creating a view from this, will there be any problems when inserting data into the view?
I might aswell add that I'm actually working on asp.net's new membership and roles, so I don't want to change the default tables.
Off of the MSDN SQL Server 2005 Enterprise Edition, what is the MSI I should use to install SQL Server 2005.
I have to Upload all files to a server, and this is going to be painfully slow. Can anyone tell me what the minimum is to upload and install SQL Server Enterprise (not Express)?
I have a reporting services application that needs to run reporting services without a user context. However, it will not pass the SSL check. I am using a self-signed certificate that is in the trusted certificates store. What I would really like to do is run reporting services without enforcing SSL (just not allow port 80 connections from the firewalll for security) and merely allow SSL connections when they come in so that any local programs can use reporting services without the need for SSL. As far as I know, Reporting Services (ISAPI filter?) is somehow checking the http headers and not allowing, at least an anonymous, a connection *unless* SSL is forced (WHY?). If my app redirects using the https://FQDN or http://localhost to reporting services it fails the SSL check.
Is there a way around this or can simply do something with the certificate store, like trusted certificates or publishers, so that my local programs can pass the SSL check?
sql server 2000 windows 2000 authentication is through the db (not windows).
I am attempting to limit access to one of our production sql server databases. I want to ensure that only application users can connect to the db, i.e they connect to the application and the application connects to the database (database access is transparent to them). The app uses a common login account.
I would like to prevent users from connecting to the db using ad hoc query tools or other EDIs (toad for sql server, ...). Currently there is nothing stopping them from doing so using the application acct/pwd.
I am contemplating writing a trigger that checks for the program that the user is using and if the program is not recognized, kill the session. Is this the right approach or is/are there any "out of the box" utilities that will do this for me?
FYI. the acct/pwd that the app uses to access the db is well known to users.
I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.
Sample Table ID DeviceID Temp InsertDate 1 10 55 04-28-2015 8:00 AM 2 65 74 04-28-2015 8:00 AM 3 44 23 04-28-2015 8:00 AM 4 10 87 04-28-2015 8:01 AM 5 65 65 04-28-2015 8:01 AM
What are the minimum permissions required by the SQL Server 2005 Upgrade Advisor (UA)? I could not find it in the documentation.
Obviously being a local Administrators Windows group and a member of sysadmin SQL Server role will do the trick.
But will being a member of only the sysadmin SQL Server role be enough? I know that the UA does want to read the registry.
Running it under just sysadmin generates the following type of errors:
Database Server PreUpgrade Requested registry access is not allowed. WINSOCKPROXY
Database Server PreUpgrade Requested registry access is not allowed. FTUNSIGNEDCOMPONENTS
Database Server PreUpgrade Requested registry access is not allowed. NETPROTOCOL
Database Server PreUpgrade Requested registry access is not allowed. FTMULTIPLEINSTANCES
Database Server PreUpgrade Requested registry access is not allowed. INVALIDNAMEDPIPE
Database Server PreUpgrade Requested registry access is not allowed. FTCOMPONENTREG
Database Server PreUpgrade Requested registry access is not allowed. FTACCTPASS
The issue then is whether these are significant or not. If the UA is only reading the registry to determine if SSAS, DTS, etc is installed then that is not important. But if it is affecting the end result because it cannot read critical information from the registry that is another matter.
I do get the warning about Minimum Hardware Requirement, but can find nothing in the detail requirements to exclude my PCs configuration.
I have installed:
IE 6.0 sp1
IIS as a part of Win 2000 Professional Edition SP4
I have also used ..aspnet_regiis -r to register ASP.NET 2.0
.NET Framework 2.0
SQL Server Native Client
SQL Server Setup support files
PC includes:
Pentium III 800 MHz
512 MB memory
HardDrive C: 5.92 GB (1.37 GB free)
HardDrive D: 13.1 GB (9.11 GB free)
I have tried several times to install SQL Server 2005 Developer Edition (most recently from MSDN Disk 3097.1) I have tried to complete an uninstall before each attempt.
I have a VB.NET program which creates a Job (Microsoft.SqlServer.Management.Smo.Agent.Job) and schedules it, to load and run a existing SSIS package, which is on a different server.
I'm using the following assemblies for this purpose.
To make this application run in my application server, do i have to install any SQL server components or SSIS rumtime components? Or Is it enough if i copy only the above mentioned dlls to my applications bin directory, in my application?
Note: I have the enterprise edition of SQL Server 2005 installed on my database server.
Running SQL2005 and on occasion I see durations of 928809 or even 2830562 in the trace for commands that usually run in the 7000 range. Reads are still in the 1200's even during the huge duration spikes. These are calls from ASP.NET ADO.NET and there should be a 30 second timeout on them by default to boot. I see a few other times that we've logged timeouts so I know the timeout is working at times but not at others. Any ideas what's going on both with the strange serious outliers and the fact that these queries aren't timed out properly?
(1) contains a for loop task (in which all the logic is contained) that loops through a particular folder for excel files WITHIN THE FOR LOOP: (2) pulls data from an excel file into SQL Tables(Data Transformation Task) (3) run stored proc to validate data (Execute SQL TASK) (4)ON SUCCESS of executing the SQL Task (Script Task - move file to success or reject folder based on value returned from sproc) (5)ON FAILURE of executing the stored proc (Script Task - move file to bad format failure)
NOTE: I have modified the MaximumErrorcount property of (1 FOR LOOP) and (3 EXECUTE SQL TASK) and the package itself to 0. In order to deal with badly formatted excel files...I do not want the package to stop for every missing tab in excel file or data entry error. I simply want the badly formatted file to be moved to a special folder
PROBLEM: on failure logic is never executed (I have 2 options after step (3)) on success do step (4) on failure do step (5) step (3) fails...then it simply iterates to the next file step 5 is never executed
Is this because I changed the maximumerrorcount property? What am I doing wrong witht he Precendence Logic?
This doesn't make any sense. I am trying to install SQL Server 2005 on SBS 2003 with Service Pack 1. According to Windows Update, there is nothing left for me to install. However, I am getting this error (while installing SQL Server Express and the Developer Edition):
"Your operating system does not meet Service Pack level requirements for this SQL Server release. Install the Service Pack from the Microsoft Download Center at http://go.microsoft.com/fwlink/?LinkId=50380, and then run SQL Server setup again."
When I go to the link, there is nothing there to download (it takes me to the main Microsoft download page). I meet all the requirements that I have found. What gives?
I am trying to upgrade the MSSQL Express with the Advanced Services version. I have:
Mobil Intel Pentium 4-M CPU 2.00 GHz
512 MB or RAM
50.8 GB free space on my C drive I am getting the following message:
- Minimum Hardware Requirement (Warning)
Messages
Minimum Hardware Requirement
The current system does not meet the minimum hardware requirements for this SQL Server release. For detailed hardware and software requirements, see the readme file or SQL Server Books Online
I have looked at the requirements in the books and it seems that I meet or exceed the minimums.
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!' CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs' CREATE SYMMETRIC KEY ERKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
Hi All,How do I set just enough permissions on a database so that a developer cancreate new objects and modify them without giving him administratorprivilege. At the moment the db objects are dbo and execute permissions aregranted to indevidual through a schema.GRANT EXECUTE ON [dbo].[My_Prcsedure] TO [MySchema]Thanks
what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.
What I try to do:
I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):
Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)
like this sqlstatement:
SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime) THEN 365 ELSE 0 END AS Expr2, FROM dbo.Phases WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)
instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.
I want to be able to pull the records with the earliest date 2011-02-06 ...
There were other records with this same customer and item number. I used this script to return the two above.
select * from price where end_dt > getdate()
Now I need to add something so it only returns the record with the earliest date. I'm going to run this on a table that has many customer and item combinations.
