Search Results

Search found 43145 results on 1726 pages for 'sql select'.

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 144/1726 | < Previous Page | 140 141 142 143 144 145 146 147 148 149 150 151  | Next Page >

  • SQL Server 2005: When copy table structure to other database "CONSTRAINT" keywords lost

    - by StreamT
    Snippet of original table: CREATE TABLE [dbo].[Batch]( [CustomerDepositMade] [money] NOT NULL CONSTRAINT [DF_Batch_CustomerDepositMade] DEFAULT (0) Snippet of copied table: CREATE TABLE [dbo].[Batch]( [CustomerDepositMade] [money] NOT NULL, Code for copy database: Server server = new Server(SourceSQLServer); Database database = server.Databases[SourceDatabase]; Transfer transfer = new Transfer(database); transfer.CopyAllObjects = true; transfer.CopySchema = true; transfer.CopyData = false; transfer.DropDestinationObjectsFirst = true; transfer.DestinationServer = DestinationSQLServer; transfer.CreateTargetDatabase = true; Database ddatabase = new Database(server, DestinationDatabase); ddatabase.Create(); transfer.DestinationDatabase = DestinationDatabase; transfer.Options.IncludeIfNotExists = true; transfer.TransferData();

    Read the article

  • How to index a table with a Type 2 slowly changing dimension for optimal performance

    - by The Lazy DBA
    Suppose you have a table with a Type 2 slowly-changing dimension. Let's express this table as follows, with the following columns: * [Key] * [Value1] * ... * [ValueN] * [StartDate] * [ExpiryDate] In this example, let's suppose that [StartDate] is effectively the date in which the values for a given [Key] become known to the system. So our primary key would be composed of both [StartDate] and [Key]. When a new set of values arrives for a given [Key], we assign [ExpiryDate] to some pre-defined high surrogate value such as '12/31/9999'. We then set the existing "most recent" records for that [Key] to have an [ExpiryDate] that is equal to the [StartDate] of the new value. A simple update based on a join. So if we always wanted to get the most recent records for a given [Key], we know we could create a clustered index that is: * [ExpiryDate] ASC * [Key] ASC Although the keyspace may be very wide (say, a million keys), we can minimize the number of pages between reads by initially ordering them by [ExpiryDate]. And since we know the most recent record for a given key will always have an [ExpiryDate] of '12/31/9999', we can use that to our advantage. However... what if we want to get a point-in-time snapshot of all [Key]s at a given time? Theoretically, the entirety of the keyspace isn't all being updated at the same time. Therefore for a given point-in-time, the window between [StartDate] and [ExpiryDate] is variable, so ordering by either [StartDate] or [ExpiryDate] would never yield a result in which all the records you're looking for are contiguous. Granted, you can immediately throw out all records in which the [StartDate] is greater than your defined point-in-time. In essence, in a typical RDBMS, what indexing strategy affords the best way to minimize the number of reads to retrieve the values for all keys for a given point-in-time? I realize I can at least maximize IO by partitioning the table by [Key], however this certainly isn't ideal. Alternatively, is there a different type of slowly-changing-dimension that solves this problem in a more performant manner?

    Read the article

  • UNIQUE Constraints in SQL (SQL Server)

    - by rockbala
    Why are UNIQUE Constraints needed in database ? Can you provide any examples ? Primary Key is UNIQUE by default... Understandable as they are referred in other tables as Foreign keys... relation is needed to connect them for rdbms platform... but why would one refer to other columns as UNIQUE, what is benefit of doing so ?)

    Read the article

  • SQL Server - Error when trying to reference a .mdf file

    - by Amokrane
    Hi, For a NUnit test I need to reference a .mdf file from a .config file. Unfortunately, I get the following error message: The FOR ATTACH option requires that at least the primary file be specified. An attempt to attach an auto-named database for file C:\....\*.mdf A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. I looked for this error on google but didn't find anything that helped me solve my problem. Any idea? Thank you

    Read the article

  • error come to execute the stored procedure using with open xml in sql server

    - by Muthumari
    Hi, i execute the below stored procedure.but it shows the error. The error is 'Incorrect syntax near '.'.i.e error shows in 'xmlFields.Country' please look this stored procedure also and help me thanks, in advance create procedure sp_SuUpdateSUADUsersStatus ( @FinalEMPCode nvarchar(50), @xmlFields NTEXT ) AS DECLARE @CityIDReturn INT SET NOCOUNT ON BEGIN DECLARE @hdoc INT EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlFields BEGIN EXEC @CityIDReturn=sp_SuSaveADUsersLocation @Country=xmlFields.Country, xmlFields.State,xmlFields.City FROM OPENXML(@hDoc, 'EmpCode/User', 2) WITH (Country nvarchar(500),State nvarchar(500),City nvarchar(500)) as xmlFields where xmlFields.Country <>'' and xmlFields.State <>'' and xmlFields.City <>'') END EXEC sp_xml_removedocument @hdoc End

    Read the article

  • How to model a mutually exclusive relationship in sql server

    - by littlechris
    Hi, I have to add functionality to an existing application and I've run into a data situation that I'm not sure how to model. I am being restricted to the creation of new tables and code. If I need to alter the existing structure I think my client may reject the proposal..although if its the only way to get it right this is what I will have to do. I have an Item table that can me link to any number of tables, and these tables may increase over time. The Item can only me linked to one other table, but the record in the other table may have many items linked to it. Examples of the tables/entities being linked to are "Person", "Vehicle", "Building", "Office". These are all separate tables. Example of Items are "Pen", "Stapler", "Cushion", "Tyre", "A4 Paper", "Plastic Bag", "Poster", "Decoration" For instance a "Poster" may be allocated to a "Person" or "Office" or "Building". In the future if they add a "Conference Room" table it may also be added to that. My intital thoughts are: Item { ID, Name } LinkedItem { ItemID, LinkedToTableName, LinkedToID } The LinkedToTableName field will then allow me to identify the correct table to link to in my code. I'm not overly happy with this solution, but I can't quite think of anything else. Please help! :) Thanks!

    Read the article

  • Best way to stop SQL Injection in PHP

    - by Andrew G. Johnson
    So specifically in a mysql database. Take the following code and tell me what to do. // connect to the mysql database $unsafe_variable = $_POST["user-input"]; mysql_query("INSERT INTO table (column) VALUES ('" . $unsafe_variable . "')"); // disconnect from the mysql database

    Read the article

  • SSIS - Skip Missing Files

    - by Greg
    I have a SSIS 2008 package that calls about 10 other SSIS packages (legacy issues, don't ask). Each of those child packages loads a specific file into a table. But sometimes one or more of these input files will be missing. How can I let a child package fail (because a file is missing) but let the rest of the parent package keep on running? I've tried increasing the maximum error count on the parent package, the tasks in the parent package that call each child, and in the child package itself. None of that seemed to make any difference. I still get this error when I run it with a file missing: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) 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. Edit: failpackageonfailure and faulparentonfailure are already all set to false everywhere.

    Read the article

  • SQL Server compare table entries for update

    - by Dave
    I have a trade table with several million rows. Each row represents the version of a trade. If I'm given a possibly new trade I compare it to the latest version in the trade table. If it has changed I add a new version, otherwise I do nothing. In order to compare the 2 trades I read the version from the trade table into my application. This doesn't work well when I'm given 10s of thousands of possibly new trades. Even batching reads to read in a 1000 trades at once and compare them the whole process can take several minutes. All the time is spent in the DB. I'm trying to find a way to compare the possibly new trades to the ones in the trade table without so much I/O. What I've come up with so far is adding a hash column to each row in the trade table. The hash is of all the trade fields. Then when I'm given possibly new trades I compute their hash, put the values into a temporary table, then find ones that are different. This feels very hacky. Is there a better way of doing it? Thanks

    Read the article

  • How to create relationship between two tables with revisions using Entity Framework

    - by Chris Ridenour
    So I am in the process of redesigning a small database (and potentially a much larger one) but want to show the value of using revisions / history of the business objects. I am switching the data from Access to MSSQL 2008. I am having a lot of internal debate on what version of "revision history" to use in the design itself - and thought I had decided to add a "RevisionId" to all tables. With this design - adding a RevisionId to all tables we would like tracked - what would be the best way to create Navigational Properties and Relationships between two tables such as | Vendor | VendorContact | where a Vendor can have multiple contacts. The Contacts themselves will be under revision. Will it require custom extensions or am I over thinking this? Thanks in advance.

    Read the article

  • SQL Server unique constraint problem

    - by b0x0rz
    How to create a unique constraint on a varchar(max) field in visual studio, visually. the problem is when i try it: manage indexes and keys add columns I can only chose the bigint columns, but not any of the varchar(max) ones. Do I maybe have to use check constraints? If yes, what to put in the expression? Thnx for the info

    Read the article

  • Picking Random Names

    - by Jasl
    I saw an interesting post sometime back but with no solution. Trying luck here: There is a table which contain 10 names (U1, U2, U3..and so on). I have to choose 5 names everyday, and display one as the Editor and 4 as Contributors While selecting the random names, I have to also consider that if one user is selected as Editor, he cannot become editor again till everyone got their chance. The output should look similar to the following: Editor Cont1 Cont2 Cont3 Cont4 20-Jun U1 U8 U9 U3 U4 21-Jun U7 U2 U5 U6 U10 22-Jun U3 U4 U9 U2 U8 23-Jun U4 U8 U3 U5 U2 and so on..

    Read the article

  • Join Where Rows Don't Exist or Where Criteria Matches...?

    - by Greg
    I'm trying to write a query to tell me which orders have valid promocodes. Promocodes are only valid between certain dates and optionally certain packages. I'm having trouble even explaining how this works (see psudo-ish code below) but basically if there are packages associated with a promocode then the order has to have one of those packages and be within a valid date range otherwise it just has to be in a valid date range. The whole "if PrmoPackage rows exist" thing is really throwing me off and I feel like I should be able to do this without a whole bunch of Unions. (I'm not even sure if that would make it easier at this point...) Anybody have any ideas for the query? if `OrderPromoCode` = `PromoCode` then if `OrderTimestamp` is between `PromoStartTimestamp` and `PromoEndTimestamp` then if `PromoCode` has packages associated with it //yes then if `PackageID` is one of the specified packages //yes code is valid //no invalid //no code is valid Order: OrderID* | OrderTimestamp | PackageID | OrderPromoCode 1 | 1/2/11 | 1 | ABC 2 | 1/3/11 | 2 | ABC 3 | 3/2/11 | 2 | DEF 4 | 4/2/11 | 3 | GHI Promo: PromoCode* | PromoStartTimestamp* | PromoEndTimestamp* ABC | 1/1/11 | 2/1/11 ABC | 3/1/11 | 4/1/11 DEF | 1/1/11 | 1/11/13 GHI | 1/1/11 | 1/11/13 PromoPackage: PromoCode* | PromoStartTimestamp* | PromoEndTimestamp* | PackageID* ABC | 1/1/11 | 2/1/11 | 1 ABC | 1/1/11 | 2/1/11 | 3 GHI | 1/1/11 | 1/11/13 | 1 Desired Result: OrderID | IsPromoCodeValid 1 | 1 2 | 0 3 | 1 4 | 0

    Read the article

  • "lock request time out period exceeded" Error When Trying to See DB Hierarchies

    - by Lloyd Banks
    I have a DB that I can run basic queries (albeit much slower than normal) off of. When I try to see the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I get the "lock request time out period exceeded". My Report Server reports that run off of objects in this DB are no longer completing. Jobs associated with procedures stored on this DB also do not run. I tried using sp_who2 to find and kill all connections on the DB. This has not solved the problem. What is going on here? How can I resolve this?

    Read the article

  • T-SQL query and group by reporting help

    - by Dayton Brown
    So I have some data that looks like this. `USERID1 USERID2` 1 10 2 20 2 30 3 40 3 50 1 10 2 20 2 30 3 50 I want a query that produces the following `USERID1 COUNT` 2 2 3 2 It's a group by query that shows me the count of unique USERID2 for each USERID1 that has more than 1 USERID2 associated with it. God I hope you aren't as confused as I am by that last statement.

    Read the article

  • INSERT statement conflicted with the FOREIGN KEY constraint

    - by SmartestVEGA
    I am getting the following error. Could you please help me in ? Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sup_Item_Sup_Item_Cat". The conflict occurred in database "dev_bo", table "dbo.Sup_Item_Cat". The statement has been terminated. insert into sup_item (supplier_id,sup_item_id,name,sup_item_cat_id,status_code,last_modified_user_id,last_modified_timestamp,client_id) values(10162425,10,'jaiso','123123','a','12','2010-12-12','1062425') the last coulum "client_id" i am getting the conflict. I tried to put the value which already exists in the dbo.Sup_Item_Cat to the column corresponding to the sup_item.. but no joy :-(

    Read the article

  • Insert a Join Statement - (Insert Data to Multiple Tables) - C#/SQL/T-SQL/.NET

    - by peace
    I have a Winform that has fields need to be filled by a user. All the fields doesn't belong to one table, the data will go to Customer table and CustomerPhone table, so i decided to do multiple inserts. I will insert appropriate data to CustomerPhone first then Insert the rest data to Customer table. Is it possible to Join an Insert OR Insert a Join? If show me a rough sample, i will be grateful. Many Thanks

    Read the article

  • Using "CASE" in Where clause to choose various column harm the performance

    - by zivgabo
    I have query which needs to be dynamic on some of the columns, meaning I get a parameter and according its value I decide which column to fetch in my Where clause. I've implemented this request using "CASE" expression: (CASE @isArrivalTime WHEN 1 THEN ArrivalTime ELSE PickedupTime END) >= DATEADD(mi, -@TZOffsetInMins, @sTime) AND (CASE @isArrivalTime WHEN 1 THEN ArrivalTime ELSE PickedupTime END) < DATEADD(mi, -@TZOffsetInMins, @fTime) If @isArrivalTime = 1 then chose ArrivalTime column else chose PickedupTime column. I have a clustered index on ArrivalTime and nonclustered index on PickedupTime. I've noticed that when I'm using this query (with @isArrivalTime = 1), my performance is a lot worse comparing to only using ArrivalTime. Maybe the query optimizer can't use\choose the index properly in this way? I compared the execution plans an noticed that when I'm using the CASE 32% of the time is being wasted on the index scan, but when I didn't use the CASE(just usedArrivalTime`) only 3% were wasted on this index scan. Anyone know the reason for this?

    Read the article

  • Is it OK to re-create many SQL connections (SQL 2008)

    - by Mr. Flibble
    When performing many inserts into a database I would usually have code like this: using (var connection = new SqlConnection(connStr)) { connection.Open(); foreach (var item in items) { var cmd = new SqlCommand("INSERT ...") cmd.ExecuteNonQuery(); } } I now want to shard the database and therefore need to choose the connection string based on the item being inserted. This would make my code run more like this foreach (var item in items) { connStr = GetConnectionString(item); using (var connection = new SqlConnection(connStr)) { connection.Open(); var cmd = new SqlCommand("INSERT ...") cmd.ExecuteNonQuery(); } } Which basically means it's creating a new connection to the database for each item. Will this work or will recreating connections for each insert cause terrible overhead?

    Read the article

  • SQL Server unique contraint problem

    - by b0x0rz
    How to create a unique constraint on a varchar(max) field in visual studio, visually. the problem is when i try it: manage indexes and keys add columns i can only chose the bigint columns, but not any of the varchar(max) ones. do i maybe have to use check constraints? if yes, what to put in the expression? thnx for the info

    Read the article

  • Files and filegroups sql server 2005

    - by Dhivagar
    Can we move default file to another filegroup. sample code is given below Sample code create database EMPLOYEE ON PRIMARY ( NAME = 'PRIMARY_01', FILENAME = 'C:\METADATA\PRIM01.MDF', SIZE = 5 MB , MAXSIZE =50 MB, FILEGROWTH = 2 MB), ( NAME = 'SECONDARY_02', FILENAME = 'C:\METADATA\SEC02.NDF' ), FILEGROUP EMPLOYEE_dETAILS ( NAME = 'EMPDETILS_01', FILENAME = 'C:\METADATA\EMPDET01.NDF', SIZE = 5 MB , MAXSIZE =50 MB, FILEGROWTH = 2 MB), ( NAME = 'EMPDETILS_02', FILENAME = 'C:\METADATA\EMPDET02.NDF', SIZE = 5 MB , MAXSIZE =50 MB, FILEGROWTH = 2 MB) LOG ON ( NAME = 'TRANSACLOG', FILENAME ='c:\METADATA\TRAS01.LDF', SIZE = 5 MB , MAXSIZE =50 MB, FILEGROWTH = 2 MB ) now i want to move the FILENAME = 'C:\METADATA\SEC02.NDF' from deault primary file to the FILEGROUP EMPLOYEE_dETAILS ? need assist ??

    Read the article

  • SQL Server 2005 - Building a WHERE clause

    - by user336786
    Hello, I have a stored procedure that is dynamically building a query. The where clause associated with this query is based on filter values selected by a user. No matter what I do though, the where clause does not seem to get set. -- Dynamically build the WHERE clause based on the filters DECLARE @whereClause as nvarchar(1024) IF (@hasSpouse > -1) BEGIN IF (@hasSpouse = 0) SET @whereClause='p.[HasSpouse]=0' ELSE SET @whereClause='(p.[HasSpouse]=1 OR p.[HasSpouse] IS NULL)' END -- Dynamically add the next filter if necessary IF (@isVegan > -1) BEGIN IF (LEN(@whereClause) > 0) BEGIN SET @whereClause = @whereClause + ' AND ' END IF (@isVegan = 0) SET @whereClause = @whereClause + 'c.[IsVegan]=0' ELSE SET @whereClause = @whereClause + '(c.[IsVegan]=1 OR c.[IsVegan] IS NULL)' END PRINT @whereClause The @whereClause never prints anything. In turn, the LEN(@whereClause) is always NULL. The @isVegan and @hasSpouse values are passed into the stored procedure. The values are what I expected. What am I doing wrong? Why is the @whereClause never being set? Thank you for your help! Thank you!

    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 | 140 141 142 143 144 145 146 147 148 149 150 151  | Next Page >