Can someone write for me an example query that would select all the constraints that are applied to specific table?
something like:
SELECT
FieldThatHasConstraint,
FieldTableName,
TableToWhitchThisFieldHasConstraint,
FieldOfTableToWhitchThisFieldHasConstraint
TypeOfConstraint
FROM
???
WHERE
TableThatIWantToSearchForConstraints='myTable'
Is Microsoft full of #*$#*% (again) or am I badly misunderstandingsomething?Quote from Microsoft's T-SQL doc:[color=blue]> INSTEAD OF triggers are executed instead of the triggering action.> These triggers are executed after the inserted and deleted tables> reflecting the changes to the base table are created, but before any> other actions are taken. They are executed before any constraints,[/color]^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^[color=blue]> so can perform preprocessing that supplements the constraint actions.[/color](SQL Server 2000 sp3a)CREATE TABLE t (a INT PRIMARY KEY,b CHAR(1) NOT NULL)I want to override the value of [b] with the value of 'X' wheninserting into t...CREATE TRIGGER t_tbi ON t INSTEAD OF INSERT AS BEGINSET NOCOUNT ONINSERT INTO t (a,b) (SELECT a,'X' FROM inserted)ENDLet's try it...INSERT INTO t (a,b) VALUES(1,'z')SELECT * FROM ta | b---|---1 | XGood, the trigger did what it was supposed to. Lets try aslight variation...INSERT INTO t (a) VALUES(2)Server: Msg 233, Level 16, State 2, Line 1The column 'b' in table 't' cannot be null.WTF? What was that I just read about "[instead-of triggers]are executed before any constraints"?!?!What's going on here???
View 8 Replies View RelatedHi,I was wondering how to do this.I have a table with two columns in design view (start date, end date).How do I set it within sql server (as constraint) or whatever that thestart date less than or equal to end date?Thanks:DHRUV
View 5 Replies View Related
Hi, I want know how can I to build a query to get all the foreignkey contrains exist between tables using the sys tables, for example if the user select this two tables:
dbo.cat_states -> with this fields -> id_state & desc
dbo.cat_universities -> with this fields -> id_state, id_university & desc_university
I want get something like this:
dbo.universities.id_state = dbo.states.id_state
tks 4 help
Leo
Hi all,
I have a big problem. I have many tables with constraints, with foreign keys. I need to create a ordered list of tables, on the top must be the basic table what has no parents, then the second level tables (those depends on the first level) the the names of third level etc.
for example:
Table A[id]
Table B[id, idc]
Table C[id, ida]
Table D[id, ida]
Table E[id, idc]
I tried it by using information_scheme but I was unsuccesfull.
The result should be:
A
C
D
B
E
Thank you,
Tom.
Hi,
I am having trouble loading tables (within the same data flow) that have a foriegn key relationship defined between them. For instance:
Table A is a parent (one side of the relationship) to Table B (many side of the relationship).
I am trying to load Table A first within the data flow and then Table B after, but I get the following error:
[OCMD EntityRole Insert [2666]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EntityRole_Entity". The conflict occurred in database "ODS", table "dbo.Entity", column 'EntityGuid'.".
I am currently using OLE DB commands to perform the inserts, I load table A and move on to then load Table B, I can see the records in Table A before trying to load Table B but for some reason Table B load still fails.
I was thinking maybe this has something to do with the transaction setting or Isolation level but have played with this to no avail (currently everything is the default - supported/serializable). Also, I am thinking maybe because the OLE DB commands are creating two seperate connections (they are using the same connection manager) the second one is unable to see the transactions from the other (first) connection (Table A)?
Is there a way around this without dropping (disabling) forigen keys before the load and adding them back in after? Would like to avoid this?
I would also like to avoid reading the data source multiple times. Everything I need is in the one source so I would like to populate multiple tables from the one source data stream instead of reading the same data 2,3 or 4 times etc.
Seems to me there must be a simple explanation/solution for this but I'm stuck at this point?
P.S.
I was intially using OLE DB destinations (because they are much faster) and was having the same issue, which made sense because the OLE DB destinations do not let you pass the data stream on so I had to multi cast to the destinations so they were loading at the same time. I would rather use the OLE DB destinations so if you have any ideas around how I could do this using those components that would be appreciated too!
Thanks!
Hi,
I have a slight problem which I'm sure must be a common happening. Here's my problem.
I'm using Merge replication and I have a table the has a unique contraint on a non primary key column (the column is called [name]). The thing that goes wrong (for me) is that when a new record is added in a subscriber and a new record is added in the publisher before a synchronization and both records have the same [name] value then when the merge agent runs I get an unresolved conflict because of a unique index violation.
I've read the BOL and I'm left thinking that in order to solve this problem then I must use a custom resolver. Is this the best way of handling such a conflict? Actually if it is I'm still a little stuck as I'm not sure what I could do to help the situation inside the custom resolver anyway!!
Any help would be much appreciated.
Thanks
Graham
Hi all,
I was wondering if there is an easy way to loop through all contraints in a database and programmatically set the cascade delete to ON. I have a database with hundreds of contraints, so individually setting cascade delete on them is not optimal.
Thanks for any info in advance!
I think that the constraints are simply held in one of the system datatables, is there anyway to simply update that table?
Is there any easy way to truncate a table which has a foreign key restraint? I want to override the default behavior which is to not allow truncate of parent tables. I want to be able to temperarily remove the contraint so I can truncate the temple, how do you do this?
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
My partial db schema is as follows:
participants
---DID (pk) char(1)
---LID (fk - schools) char(4)
---studentLast varchar(50)
---studentFirst varchar(25)
Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================
enrollhist (insert/update trigger for enrollactive)
---EID (pk - autonumber) bigint(8)
---EMID (fk - enrollmode) int(4)
---DID (fk - participants) char(10)
---LID (fk - schools) char(4)
---enrollactive bit(1)
Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================
enrollmode
---EMID (pk) int(4)
---mode varchar(25)
Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================
schools
---LID (pk) varchar(4)
---CTID (fk - caltracks) char(1)
---AID (fk - agencies) char(1)
---SDID (fk - schooldist) char(1)
---COID (fk - countydist) char(1)
---sitename varchar(25)
---sitetitle varchar(75)
Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================
caltracks
---CTID (pk) char(1)
---legend char(4)
---trktitle varchar(15)
---trkcnt int(4)
Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================
agencies
---AID (pk) char(1)
---legend varchar(4)
---agencytitle varvhar(50)
Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?
How do you make a live link in SQL 7 to another database? I currently do such
things in Access 97 but how do you do it in SQL? I just want to keep a permanent live link to a table in another database.
Thank you in advance.
Hi,
A problem.................
Actualy in my local the work is fine..
when i shifted the files to live...
it is saying invalid column name ,even though iam having that column in the table.....
when i had executed the query in live it is working...
The database to which iam connecting is also correct.
Thanks
I am looking for a good introduction into the handling of certificates in SQL 2005.
I need to sign a procedure to allow it to access a dm view.
I do understand the theory and the syntax, but I have trouble coming up with an easy but still secure way to create these certificates on all customer servers without allowing misuse.
All articles I could find are going through creating a new database, setting up a certificate with or without password, signing an example proc and then dropping the database.
Non seems to care about the problems that occur later on during the life of a certificate.
Thanks
Hi:I have a site that is getting close to production. I am using SQL Server 2005 express and VWD express. Up to this point, I have been updating the live server by deleting the file and copying the MDF file from my development machine in its place. This clearly won't work once the site goes live - but I still expect to be doing a lot of upgrading of stored procedures etc. Can someone point me to a resource or make any suggestions that would help me in this regard?Thanks,Roger Swetnam
View 3 Replies View RelatedI'm trying to configure log shipping When I retore the full disk dump of my databaseto my standby server, I need to be able to go in and drop and recreate several user logins(so they can do reports, read-only).
Once I do this though, I will be unable to restore T-logs, right?
Anyone know a way to get it back into a read only state and able to accept T-log restores? Thanks.
Pete K.
I need to create a DB version upgrade schema on client site that will preserve the existing data.
Provide the user with a patch that he can run on his database that wiil apply changes to his DB structure and keep the old data.
One approach that I am thinking of is to create a differential DB patch - a and apply that to the client database.
Is there any strategy any algorithm to do that. Can it be automated?
If there any accomplishments or approaches known on the subject?
I would greatly appreciate any ideas you can come up with.
Zaven
I have a sql express database with a couple tables of information. I have a user modifying one of these tables with a data access page using Office Web Controls.
To backtrack a bit, the sql db used to be an access db. The same user modifying the data the same way. I also had a asp page making odbc calls to the access db to lookup information. But if the data access page was open, the odbc page would error. So i moved the data to sql.
Now, I'm getting ready to setup an application to make odbc calls to the sql db. I still want the user to modify the data using the DAP. Should I allow the user to modify the live data used for the app or should I make a copy of the data everytime its updated?
Hi, sorry if this has been asked before but I'm pretty strapped for time...
I have two tables: [photos] and [photoFolders]
[photoFolders] contains information about photo albums on the site im creating. The information in [photos] lists information about all photos along with which [photoFolder] they belong. When the user logs in, I want to present a list of all 'folders' in their name along with the TOP image with the corresponding folderId...
[photos]
photoId
photoName
folderId
photoDescription
cUserId
[photoFolders]
folderId
folderName
eventDate
cUserId
Any help would be GREATLY appreciated
We're all going to hell... I guess I'll see you there!
do i need a host with MS SQL SERVER? how do i upload my database? hmm
View 3 Replies View Relatedtrying to make a script to view data in a excell spreadsheet.I can get all the data I need except one.select ordernumber, itemcode, quantity, rate, totalfrom dbo.chargeswhere ordernumber = '45676'the problem is there are more than one itemcodeexample: run resultsordernumber, itemcode, quantity, rate, total45676 fuel 123 .10 12.3045676 stops 3 50 150how do I get this data on one line?to look more like this: run resultsordernumber, itemcode, quantity, rate, total itemcode, quantity, rate, total45676 fuel 123 .10 12.30 stops 3 50 150now just one ordernumber appears and its all on one line.hope you can help, thanks for your time...Mike
View 1 Replies View Related
Hi There
I have issues around forwarding message being dropped because they exceed the forwarding message time to live.
I cannot find anything in BOL or the net regarding changing the message time to live value to be higher, does anyone know where i change this setting ?
Thanx
We€™re doing a website for a TV station, and they want to have a live TV broadcast on their website, our experience is basically in CMS and portal solutions, but we never had any experience in live streaming, so this is all new to us, so we€™re looking for a technical and financial proposal with your suggestions and recommendations, this is the details of the project with some of the inquiries we have:
-The signal is SDI (Pal 25 fps)
-The output (online streaming) 15 fps
-The user will have two options to view the movie in:
1. Low: 56K
2. High: 300K
-The video should come within a customized designed page, probably as a code we can place inside an html table€™s cell, not as a link for external link
-We€™re expecting about 200 users for the video each month; each user€™s logon time expected 10 min.
-We have a Red hat Linux on our server that will host the site, and we use php/MySql technology.
Inquires:
1. Why Windows media is better then Real in terms of performance, can you provide us with a comparison table to include it to our client?
2. If we choose Windows media, what are the requirements we need to have, form both; the TV station side, and in our Linux server? And how much would it cost?
4. Is there any hardware required from the TV station side such as DVR, or some sort of encoder, if so; what€™s you€™re recommendations (type/ brand), please note that this is a TV station and it needs a high quality professional hardware
5. How (and this is probably the most ambiguous task)
5.1. Do you broadcast the digital signal coming from the encoder (I guess) to the server?
5.2. And then place this signal on a PHP page? Do you give us a link to embed in our code?
6. If we choose to host the live streaming on a separated server and include the URL in our code, would that still required from our server to support Windows media, or it doesn€™t matter as long as the video streaming is hosted on a Windows streaming server
7. How much disk space does it need to host a live streaming (with the details mentioned above)?
We would really appreciate your comments and suggestions, as this is our 1st time to dealing with live streaming.
I moved one of the ssis packages from dev to prod.
Did the same for the configuration file which contains the connectionstring.
Now I would like to run the package in live but the error is:
Description: The configuration file name "S:ImportsTradesEnvironment.dtsConfig" is not valid. Check the configuration file name.
The file does indeed exist and the connectionstring has the correct string.
Any thoughts please?
I'm developing a web app using ASP.NET and SQL Server 2005 Express. So far it's all been on my local computer, it hasn't gone live yet, so if I need to add a column to a table or make some other schema change I just do it right in Visual Studio, nice and simple. If I have to delete all the old content and start over, no problem. When I deploy it to a staging server I just overwrite the existing file with my new one, losing its data in the process. But soon enough I'll be deploying this to a public web server, there will be real live data in the db, people using it when I need to make updates.
What are some common strategies for updating the schema of a database on a live server?
It's obvious that when I need to update the db I'll have to shut down the site temporarily. But my biggest question is how to keep the existing data from the live db? I obviously can't overwrite it with my local copy. I want to overwrite its schema, without touching its data. How's that done?
Thanks for some advice!
Nate
I am experiencing a situation where certain functions work perfectly when I run it on my local machine under MVS, but when I upload it to the server, then it does not?!
Here is an example:
I am using a drop down box (in a FormView) that is databound in an online submission form. When I run the application in MVS, then I can edit the records by opening the form, and selection the new value in the drop down list. The new value is then also saved to the database when I hit the update button. When I upload the code to the server, then the drop down list shows the correct information (so the databinding to the control seem to work correctly), but the new value is not saved to the database.
Here is the code for the drop down list:
"DropDownList1" runat="server" DataSourceID="SqlDataSource3"DataTextField="UserName" DataValueField="UserId" SelectedValue='<%# Bind("UserId") %>'CssClass="text">"SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT [UserName], [UserId] FROM [vw_aspnet_Users] ORDER BY [UserName]"> Here is the code updating the database with the record (I have removed some records as well as the Insert and Delete parts):
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tourism_connect1 %>"UpdateCommand="UPDATE Resorts SET typeid = @typeid, destrictid = @destrictid, UserId = @UserId, WHERE (resortid = @resortid)"OnInserted="SqlDataSource1_Inserted">
<UpdateParameters><asp:Parameter Name="typeid" /><asp:Parameter Name="destrictid" /><asp:Parameter Name="UserId" /><asp:Parameter Name="resortid" /></UpdateParameters></asp:SqlDataSource>
What I can mention further, is that if I connect to the database that is on the live server (so the application runs in MVS on my local machine, but it then retrieves the info from the online database), then it also works fine. It is just giving this issue when the online application is trying to update the values.
There is also no errors during the process.
I will appreciate any advise on how to overcome this, as I really do not know where to look anymore...
Thanks in advance!
Regards
Jan
Hello,
I have my local development evrioment and my live webserver in colo. The two different machinces both have SQL2005 and copies of roughly the same database. I use the one for developement and the other obviously for production. Currently I read in different connection strings from my web.config file for my live version v my development version so I can have different database names and connection strings.
I'm looking at moving to LINQ, but I can't figure out how I'd achive an equivelent flexibility as database names and connection strings seem to be hardcoded in to the class using it's designer. Obviously if modifing this wasn't possible, nobody would be using LINQ, so how do I work around this?
Thanks,
Joel Barsotti
I have a problem in that I have a live SQL 7.0 server as a back end to a web server for a large company in Germany and of course we also have a development server locally which is more or less the same apart from data. The ASP developers here want to upload some data to the live server and also download live data from the live server so that we have more or less the same data on both systems.
I have thought about doing this with DTS adn writing SQL scripts to merge the tables and then upload back to the live server etc. but before I do I wondered if anyone had experience of doing this before (I am sure) and could either tell me what are the best methods or package to do or point me or mail me an article that covers this sort of thing.
Of course extreme paranioa creeps in at the thought of copying down large tables from the live server etc. etc. so if anyone can help - great!
Jeff Cresswell in Hamburg Germany
At Sql Server Live, I saw Sharon Dooley using the debugger in Query Analyzer, but now that I've tried it I can't step an sp, instead, I get....Quote:
SP debugging may not work properly if you log on as 'Local
System account' while SQL Server is configured to run as a
service.
You can open Event Viwer to see details.
Do you wish to continue?
End Quote:
I'm logged in at a W2Kpro box connectig via client tools
to a W2KSVR box via Integrated NT security. SS2K is NOT
installed locally.
This might sound very dumb, but here goes..
How does one upload a MS SQL database or restore this to your live environment.
Do I just FTP, publish with SQL itself or use some or other tool.
Help would be much appreciated.
Hi,
Ive been running a ms sql 2000 database on a 1gz, 512mb ram machine for several months with no real problem.
However lately its been taking upwards of 2 seconds just to connect...
Im on windows 2003 with iis 6 and connecting like so:
SET MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "Provider=SQLOLEDB.1;UID=x;Password=x;Initial Catalog=x;Data Source=127.0.0.1"
Most queries are opening in a matter of milliseconds, how ever the occasional one will take upto a second to complete. The queries are very simple update and select statements. (UPDATE Stats SET Page_Views = Page_Views + 1, Page_Views_Daily = Page_Views_Daily + 1) etc...
Any ideas why is taking so long?
Hi
Please can someone help me with a problem im having.
I have a site which allows users to place adverts, they can place as many as they like.
In dreamweaver I have managed to set up a site which now works fine in testing (early stages), a user can log in with "user" and "password".
I wowuld like to know how it can be done so that when a user logs in, it shows them how many adverts they have running.
Example:
Live adverts = "?"
I can only get as far as knowing that I need to use "count" in the command, how do I do it so it shows only the advertiser who has logged in their results
Thanks
Hi there
I've just installed Windows Live Messenger on my PC. But it keeps telling me there is a problem with the connection, and gives me the code 80072ee7.
Althought the software says I'm online, I don't seem able to see any of my contacts online or send them instant messages.
I have worked my way through the trouble-shooting advice in the help file and none of that seems to work. My ISP is Orange. I use their broadband service with Explorer with no problems at all.
Please could you suggest what the code above means, and what I can do to overcome the problem? I'm not very computer-literate, so a simple explanation would be much appreciated.
Many thanks Friendly01
I need to restart (becouse of the Database Mail :( ) SQL Server Agent on a live server which acts as a distributor for a lot of replications. I know that it shouldn't cause any problem, but I want to confirm that it want couse a subscriptions to be reinitiated.
Thanks in advance for quick reply