Search Results

Search found 32641 results on 1306 pages for 'sql constraint and keys'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 340/1306 | < Previous Page | 336 337 338 339 340 341 342 343 344 345 346 347  | Next Page >

  • How to add Transactions with a DataSet created using the Add Connection Wizard?

    - by RoguePlanetoid
    I have a DataSet that I have added to my project where I can Insert and Add records using the Add Query function in Visual Studio 2010, however I want to add transactions to this, I have found a few examples but cannot seem to find one that works with these. I know I need to use the SQLClient.SQLTransaction Class somehow. I used the Add New Data Source Wizard and added the Tables/View/Functions I need, I just need an example using this process such as How to get the DataConnection my DataSet has used. Assuming all options have been set in the wizard and I am only using the pre-defined adapters and options asked for in this wizard, how to I add the Transaction logic to my Database. For example I have a DataSet called ProductDataSet with the XSD created for this, I have then added my Stock table as a Datasource and Added an AddStock method with a wizard, this also if a new item calls an AddItem method, if either of these fails I want to rollback the AddItem and AddStock in this case.

    Read the article

  • Restore Partioned database into multiple filegroups

    - by Renju
    does anyone have any query to restore partioned db that having multiple file groups,In the restore option in the SSME i need to edit manually all the path of the filegroups restore as option it little bit tedious as it having more than 150 filegroups eg:USE master GO -- First determine the number and names of the files in the backup. RESTORE FILELISTONLY FROM MyNwind_1 -- Restore the files for MyNwind. RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERY, MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf', MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf' GO -- Apply the first transaction log backup. RESTORE LOG MyNwind FROM MyNwind_log1 WITH NORECOVERY GO -- Apply the last transaction log backup. RESTORE LOG MyNwind FROM MyNwind_log2 WITH RECOVERY GO Here i need to specify multiple MOVE command for all my filegroups,this is a tedious task when having more than 100s of filegroups MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf', MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf' I need to move the files into the path i provided as a parameter.Please help. Regards Renju http://blog.renjucool.com

    Read the article

  • SQL Server Full Text Search Leading Wildcard

    - by aherrick
    After taking a look at this SO question and doing my own research, it appears that you cannot have a leading wildcard while using full text search. So in the most simple example, if I have a Table with 1 column like below: TABLE1 coin coinage undercoin select COLUMN1 from TABLE1 where COLUMN1 LIKE '%coin%' Would get me the results I want. How can I get the exact same results with FULL TEXT SEARCH enabled on the column? The following two queries return the exact same data, which is not exactly what I want. SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS(COLUMN1, '"coin*"') SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS(COLUMN1, '"*coin*"')

    Read the article

  • Counting consecutive items within MS SQL

    - by Greg
    Got a problem with a query I'm trying to write. I have a table that lists people that have been sent an email. There is a bit column named Active which is set to true if they have responded. But I need to count the number of consecutive emails the person has been inactive since either their first email or last active email. For example, this basic table shows one person has been sent 9 emails. They have been active within two of the emails (3 & 5). So their inactive count would be 4 as we are counting from email number 6 onwards. PersonID(int) EmailID(int) Active(bit) 1 1 0 1 2 0 1 3 1 1 4 0 1 5 1 1 6 0 1 7 0 1 8 0 1 9 0 Any pointers or help would be great. Regards Greg

    Read the article

  • Two different definitions of database schema

    - by AspOnMyNet
    a) I found two definitions of schema: FIRST - A set of information that describes a table is known as a schema, and schemas are used to describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any). SECOND - A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. I assume the two descriptions describe entirely different concepts, which just happen to use the same name? b) A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. If I understand the above definition correctly, then database schema is similar to a namespace, only difference being that we can assign access permissions to database schema, while same can’t be done with namespaces? thanx

    Read the article

  • Efficient way to update SQL 'relationship' table

    - by AmbroseChapel
    Say I have three properly normalised tables. One of people, one of qualifications and one mapping people to qualifications: People: id | Name ---------- 1 | Alice 2 | Bob Degrees: id | Name --------- 1 | PhD 2 | MA People-to-degrees: person_id | degree_id --------------------- 1 | 2 # Alice has an MA 2 | 1 # Bob has a PhD So then I have to update this mapping via my web interface. (I made a mistake. Bob has a BA, not a PhD, and Alice just got her B Eng.) There are four possible states of these one-to-many relationship mappings: was true before, should now be false was false before, should now be true was true before, should remain true was false before, should remain false what I don't want to do is read the values from four checkboxes, then hit the database four times to say "Did Bob have a BA before? Well he does now." "Did Bob have PhD before? Because he doesn't any more" and so on. How do other people address this issue? I'm curious to see if someone else arrives at the same solution I did.

    Read the article

  • SQL Selects on subsets

    - by Adam
    I need to check if a row exists in a database; however, I am trying to find the way to do this that offers the best performance. This is best summarised with an example. Let's assume I have the following table: dbo.Person( FirstName varchar(50), LastName varchar(50), Company varchar(50) ) Assume this table has millions of rows, however ONLY the column Company has an index. I want to find out if a particular combination of FirstName, LastName and Company exists. I know I can do this: IF EXISTS(select 1 from dbo.Person where FirstName = @FirstName and LastName = @LastName and Company = @Company) Begin .... End However, unless I'm mistaken, that will do a full table scan. What I'd really like it to do is a query where it utilises the index. With the table above, I know that the following query will have great performance, since it uses the index: Select * from dbo.Person where Company = @Company Is there anyway to make the search only on that subset of data? e.g. something like this: select * from ( Select * from dbo.Person where Company = @Company ) where FirstName = @FirstName and LastName = @LastName That way, it would only be doing a table scan on a much narrower collection of data. I know the query above won't work, but is there a query that would? Oh, and I am unable to create temporary tables, as the user will only have read access.

    Read the article

  • Why better isolation level means better performance in SQL Server

    - by Oleg Zhylin
    When measuring performance on my query I came up with a dependency between isolation level and elapsed time that was surprising to me READUNCOMMITTED - 409024 READCOMMITTED - 368021 REPEATABLEREAD - 358019 SERIALIZABLE - 348019 Left column is table hint, and the right column is elapsed time in microseconds (sys.dm_exec_query_stats.total_elapsed_time). Why better isolation level gives better performance? This is a development machine and no concurrency whatsoever happens. I would expect READUNCOMMITTED to be the fasted due to less locking overhead. Update: I did measure this with DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE issued and Profiler confirms there're no cache hits happening. Update2: The query in question is an OLAP one and we need to run it as fast as possible. Closing the production server from outside world to get the computation done is not out of question if this gives performance benefits.

    Read the article

  • ASP.NET MSSQL Select top N values but skip M results

    - by Mikey
    Im working on an ASP.Net project to display information on a website from a database. I want to select the top 10 items from a news table but skip the first Item and I'm having some problem with it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" SelectCommand="SELECT top 5 [id], [itemdate], [title], [description], [photo] FROM [Announcements] order by itemdate desc"> </asp:SqlDataSource> This is what I have so far but i can't find any info online about how to skip a record

    Read the article

  • Using DateDiff in Entity Framwork on a SQL CE database

    - by deverop
    I have a method which should return a list of anonymous objects with a calculated column like this: var tomorrow = DateTime.Today.AddDays(1); return from t in this.Events where (t.StartTime >= DateTime.Today && t.StartTime < tomorrow && t.EndTime.HasValue) select new { Client = t.Activity.Project.Customer.Name, Project = t.Activity.Project.Name, Task = t.Activity.Task.Name, Rate = t.Activity.Rate.Name, StartTime = t.StartTime, EndTime = t.EndTime.Value, Hours = (System.Data.Objects.SqlClient.SqlFunctions.DateDiff("m", t.StartTime, t.EndTime.Value) / 60), Description = t.Activity.Description }; Unfortunately I get the following error from the DateDiff function: The specified method 'System.Nullable1[System.Int32] DateDiff(System.String, System.Nullable1[System.DateTime], System.Nullable`1[System.DateTime])' on the type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression. Any ideas what I could have done wrong here? EDIT: I also tried the EntityFunctions class mentioned here, but that did not work as well. Minutes = EntityFunctions.DiffMinutes(t.EndTime, t.StartTime),

    Read the article

  • Duplicate all rows in sql database table

    - by Andrew Welch
    I have a table which contains house details called property. I am creating a localised application, and I have a db table called propertylocalised. In this table is held duplicates of the data and culture column e.g. key culture propertyname 1 en helloproperty 1 fr bonjourproperty At the moment I have all my en culture inserted but I want to duplicate all of those rows and then for every other row insert fr into culture. I obviously only want to do this once, for the purpose of setting up the localisation. Thanks Andy

    Read the article

  • Most optimal order (of joins) for left join

    - by Ram
    I have 3 tables Table1 (with 1020690 records), Table2(with 289425 records), Table 3(with 83692 records).I have something like this SELECT * FROM Table1 T1 /* OK fine select * is bad when not all columns are needed, this is just an example*/ LEFT JOIN Table2 T2 ON T1.id=T2.id LEFT JOIN Table3 T3 ON T1.id=T3.id and a query like this SELECT * FROM Table1 T1 LEFT JOIN Table3 T3 ON T1.id=T3.id LEFT JOIN Table2 T2 ON T1.id=T2.id The query plan shows me that it uses 2 Merge Join for both the joins. For the first query, the first merge is with T1 and T2 and then with T3. For the second query, the first merge is with T1 and T3 and then with T2. Both these queries take about the same time(40 seconds approx.) or sometimes Query1 takes couple of seconds longer. So my question is, does the join order matter ?

    Read the article

  • Help me finding dependency list.

    - by Pearl
    I have two table employee table and employee dependency table. Employee tooks like below. insert into E values(1,'Adam') insert into E values(2,'Bob') insert into E values(3,'Candy') insert into E values(4,'Doug') insert into E values(5,'Earl') insert into E values(6,'Fran') Employee dependency table looks like below insert into Ed values(3,'2') insert into Ed values(3,'5') insert into Ed values(2,'1') insert into Ed values(2,'4') insert into Ed values(5,'6') I need to find the dependency list like below Eid Ename Dname 3 Candy Bob,Fran Please help me finding the above.

    Read the article

  • How to Use .NET Assembly from Legacy SQL Server 2000 DTS

    - by shyneman
    Hi All, I have a .NET assembly that needs to be called from a DTS package. There are two options I am considering to get this to work: 1) write a COM-callable wrapper for the .NET assembly and have the VBScript create the COM object to use 2) write a .NET command-line exe that uses that .NET assembly and have the VBScript execute that exe Can anybody comment on the pros/cons of either approach and which is the better way of doing this? If there are other solutions, I'd love to hear them too. Thanks a lot for any input.

    Read the article

  • Sql query to get the GrandFather name by doing a self join

    - by mahesh
    Hello all , Can any one please give me the query to get the child name and his grand fathers name For eg - if i have a table Relationships in the i have childid and fatherid columns so how will i get the grandfather, i can easily get the father name by using a join but for grandfather i need to do joins 2 times so can any one help me with this D.Mahesh

    Read the article

  • LINQ to SQL repository - caching data

    - by creativeincode
    I have built my first MVC solution and used the repository pattern for retrieving/inserting/updating my database. I am now in the process of refactoring and I've noticed that a lot of (in fact all) the methods within my repository are hitting the database everytime. This seems overkill and what I'd ideally like is to do is 'cache' the main data object e.g. 'GetAllAdverts' from the database and to then query against this cached object for things like 'FindAdvert(id), AddAdvert(), DeleteAdvert() etc..' I'd also need to consider updating/deleting/adding records to this cache object and the database. What is the best apporoach for something like this? My knowledge of this type of things is minimal and really looking for advice/guidance/tutorial to point me in the right direction. Thanks in advance.

    Read the article

  • Return if remote stored procedure fails

    - by njk
    I am in the process of creating a stored procedure. This stored procedure runs local as well as external stored procedures. For simplicity, I'll call the local server [LOCAL] and the remote server [REMOTE]. USE [LOCAL] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[monthlyRollUp] AS SET NOCOUNT, XACT_ABORT ON BEGIN TRY EXEC [REOMTE].[DB].[table].[sp] --This transaction should only begin if the remote procedure does not fail BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp1] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp2] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp3] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp4] COMMIT END TRY BEGIN CATCH -- Insert error into log table INSERT INTO [dbo].[log_table] (stamp, errorNumber, errorSeverity, errorState, errorProcedure, errorLine, errorMessage) SELECT GETDATE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() END CATCH GO When using a transaction on the remote procedure, it throws this error: OLE DB provider ... returned message "The partner transaction manager has disabled its support for remote/network transactions.". I get that I'm unable to run a transaction locally for a remote procedure. How can I ensure that the this procedure will exit and rollback if any part of the procedure fails?

    Read the article

  • SQL Pivot table error-using variable gives syntax error

    - by Antoni
    Hi my coworker came to me with this error and now I am hooked and trying to figure it out, hope some of the experts can help us! Thanks so much! When I execute Step6 we get this error: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@cols'. --Sample of pivot query --Creating Test Table Step1 CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT) GO -- Inserting Data into Table Step2 INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',2) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','SODA',6) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','MILK',1) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','BEER',12) INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','MILK',3) INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','BEER',24) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',3) GO -- Selecting and checking entires in table Step3 SELECT * FROM Product GO -- Pivot Table ordered by PRODUCT Step4 select * FROM ( SELECT * FROM Product) up PIVOT (SUM(QTY) FOR CUST IN ([FRED], [KATE])) AS pvt ORDER BY PRODUCT GO --dynamic pivot???? Step5 DECLARE @cols NVARCHAR(2000) select @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + b.Cust FROM (select top 100 Cust from tblProduct)b ORDER BY '],[' + b.Cust FOR XML PATH('') ), 1, 2, '') + ']' --Show Step6 SELECT * FROM (SELECT * FROM tblProduct) p PIVOT (SUM(QTY) FOR CUST IN (@cols)) as pvt Order by Product

    Read the article

  • SQL How to join multiplue columns with same name to one column

    - by Choi Shun Chi
    There is a super class account {User, TYPE} and subclasses saving{User, ID, balance,TYPE,interest,curency_TYPE} time{User,ID,balance,TYPE,interest,curency_TYPE,start_date,due_date,period} fore{User,ID,balance,interest,curency_TYPE} User and TYPE is the primary key of account and foreign key of three subclasses ID is primary key of three subclasses how to make a list of showing all IDs in one column?Also the same as balance and TYPE meet the problem I considered a.ID as saving, b.ID as time but it showing them separately

    Read the article

  • PL/SQL Sum by hour

    - by Steve
    Hi, I have some data with start and stop date that I need to sum. I am not sure how to code for it. Here are is the data I have to use: STARTTIME,STOPTIME,EVENTCAPACITY 8/12/2009 1:15:00 PM,8/12/2009 1:59:59 PM,100 8/12/2009 2:00:00 PM,8/12/2009 2:29:59 PM,100 8/12/2009 2:30:00 PM,8/12/2009 2:59:59 PM,80 8/12/2009 3:00:00 PM,8/12/2009 3:59:59 PM,85 In this example I would need the sum from 1pm to 2pm, 2pm to 3pm and 3pm to 4pm Any suggestions are appreciated. Steve

    Read the article

  • Checking inherited attributes in an 'ancestry' based SQL table

    - by Brendon Muir
    I'm using the ancestry gem to help organise my app's tree structure in the database. It basically writes a childs ancestor information to a special column called 'ancestry'. The ancestry column for a particular child might look like '1/34/87' where the parent of this child is 87, and then 87's parent is 34 and 34's is 1. It seems possible that we could select rows from this table each with a subquery that checks all the ancestors to see if a certain attribute it set. E.g. in my app you can hide an item and its children just by setting the parent element's visibility column to 0. I want to be able to find all the items where none of their ancestors are hidden. I tried converting the slashes to comma's with the REPLACE command but IN required a set of comma separated integers rather than one string with comma separated string numbers. It's funny, because I can do this query in two steps, e.g. retrieve the row, then take its ancestry column, split out the id's and make another query that checks that the id is IN that set of id's and that visibility isn't ever 0 and whala! But joining these into one query seems to be quite a task. Much searching has shown a few answers but none really do what I want. SELECT * FROM t1 WHERE id = 99; 99's ancestry column reads '1/34/87' SELECT * FROM t1 WHERE visibility = 0 AND id IN (1,34,87); kind of backwards, but if this returns no rows then the item is visible. Has anyone come across this before and come up with a solution. I don't really want to go the stored procedure route. It's for a rails app.

    Read the article

  • Multiple IN statements for WHERE. Would this return good data?

    - by TheDudeAbides
    SELECT ['VISA CK - 021810$'].[ACCT NBR #1], ['VISA CK - 021810$'].[ALT CUST NM #1], ['VISA CK - 021810$'].[LAST USED] FROM ['VISA CK - 021810$'] WHERE ['VISA CK - 021810$'].[ALT CUST NM #1] IN ( SELECT ['VISA CK - 021810$'].[ALT CUST NM #1] FROM ['VISA CK - 021810$'] GROUP BY ['VISA CK - 021810$'].[ALT CUST NM #1] HAVING COUNT(['VISA CK - 021810$'].[ALT CUST NM #1]) > 1 ) AND ['VISA CK - 021810$'].[ACCT NBR #1] IN ( SELECT ['VISA CK - 021810$'].[ACCT NBR #1] FROM ['VISA CK - 021810$'] GROUP BY ['VISA CK - 021810$'].[ACCT NBR #1] HAVING COUNT(['VISA CK - 021810$'].[ACCT NBR #1]) > 1 )

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 336 337 338 339 340 341 342 343 344 345 346 347  | Next Page >