Search Results

Search found 49224 results on 1969 pages for 'database mirroring sql se'.

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 384/1969 | < Previous Page | 380 381 382 383 384 385 386 387 388 389 390 391  | Next Page >

  • Why use shorter VARCHAR(n) fields?

    - by chryss
    It is frequently advised to choose database field sizes to be as narrow as possible. I am wondering to what degree this applies to SQL Server 2005 VARCHAR columns: Storing 10-letter English words in a VARCHAR(255) field will not take up more storage than in a VARCHAR(10) field. Are there other reasons to restrict the size of VARCHAR fields to stick as closely as possible to the size of the data? I'm thinking of Performance: Is there an advantage to using a smaller n when selecting, filtering and sorting on the data? Memory, including on the application side (C++)? Style/validation: How important do you consider restricting colunm size to force non-sensical data imports to fail (such as 200-character surnames)? Anything else? Background: I help data integrators with the design of data flows into a database-backed system. They have to use an API that restricts their choice of data types. For character data, only VARCHAR(n) with n <= 255 is available; CHAR, NCHAR, NVARCHAR and TEXT are not. We're trying to lay down some "good practices" rules, and the question has come up if there is a real detriment to using VARCHAR(255) even for data where real maximum sizes will never exceed 30 bytes or so. Typical data volumes for one table are 1-10 Mio records with up to 150 attributes. Query performance (SELECT, with frequently extensive WHERE clauses) and application-side retrieval performance are paramount.

    Read the article

  • Logic in the db for maintaining a points system relationship?

    - by MarcusBooster
    I'm making a little web based game and need to determine where to put logic that checks the integrity of some underlying data in the sql database. Each user keeps track of points assigned to him, and points are awarded by various tasks. I keep a record of each task transaction to make sure they're not repeated, and to keep track of the value of the task at the time of completion, since an individual award level my fluctuate over time. My schema looks like this so far: create table player ( player_ID serial primary key, player_Points int not null default 0 ); create table task ( task_ID serial primary key, task_PointsAwarded int not null ); create table task_list ( player_ID int references player(player_ID), task_ID int references task(task_ID), when_completed timestamp default current_timestamp, point_value int not null, --not fk because task value may change later constraint pk_player_task_id primary key (player_ID, task_ID) ); So, the player.player_Points should be the total of all his cumulative task points in the task_list. Now where do I put the logic to enforce this? Should I do away with player.player_Points altogether and do queries every time I want to know the total score? Which seems wasteful since I'll be doing that query a lot over the course of a game. Or, put a trigger in the task_list that automatically updates the player.player_Points? Is that too much logic to have in the database and should just maintain this relationship in the application? Thanks.

    Read the article

  • NHibernate not dropping foreign key constraints.

    - by Kendrick
    I'm new to NHibernate, so this is probably my mistake, but when I use: schema.Create(true, true); I get: SchemaExport [(null)]- There is already an object named 'XXX' in the database. System.Data.SqlClient.SqlException: There is already an object named 'XXX' in the database. I grabbed the SQL code nHibernate was using, ran it directly from MSSMS, and recieved similar errors. Looking into it, the generated code is not properly dropping the foreign key constraints. The drop looks like this: if exists (select 1 from sysobjects where id = OBJECT_ID(N'dbo[FK22212EAFBFE4C58]') AND parent_obj = OBJECT_ID('YYY')) alter table dbo.YYY drop constraint FK22212EAFBFE4C58 Doing a "select OBJECT_ID(N'dbo[FK22212EAFBFE4C58]')" I get null. If I take out the "dbo" (i.e. "select OBJECT_ID(N'[FK22212EAFBFE4C58]')") then the ID is returned. So, my question is, why is nHibernate adding the dbo, and why does that prevent the object from being returned (since the table owning the constraint is dbo.XXX) One of my mapping files: <?xml version="1.0" encoding="utf-8"?> <hibernate-mapping namespace="CanineApp.Model" assembly="CanineApp.Model" xmlns="urn:nhibernate-mapping-2.2"> <class name="MedicalLog" table="MedicalLog" schema="dbo"> <id name="MedicalLogID" type="Int64"> <generator class="identity" /> </id> <property name="InvoiceAmount" type="Decimal" not-null="true" /> ... <many-to-one name="Canine" class="Canine" column="CanineID" not-null="true" fetch="join" /> <many-to-one name="TreatmentCategory" class="TreatmentCategory" column="TreatmentCategoryID" not-null="true" access="field.camelcase-underscore" /> </class> </hibernate-mapping>

    Read the article

  • SQL query for the latest record for each day

    - by Mac
    I've got an Oracle 10g database with a table with a structure and content very similar to the following: CREATE TABLE MyTable ( id INTEGER PRIMARY KEY, otherData VARCHAR2(100), submitted DATE ); INSERT INTO MyTable VALUES (1, 'a', TO_DATE('28/04/2010 05:13', ''DD/MM/YYYY HH24:MI)); INSERT INTO MyTable VALUES (2, 'b', TO_DATE('28/04/2010 03:48', ''DD/MM/YYYY HH24:MI)); INSERT INTO MyTable VALUES (3, 'c', TO_DATE('29/04/2010 05:13', ''DD/MM/YYYY HH24:MI)); INSERT INTO MyTable VALUES (4, 'd', TO_DATE('29/04/2010 17:16', ''DD/MM/YYYY HH24:MI)); INSERT INTO MyTable VALUES (5, 'e', TO_DATE('29/04/2010 08:49', ''DD/MM/YYYY HH24:MI)); What I need to do is query the database for the latest record submitted on each given day. For example, with the above data I would expect the records with ID numbers 1 and 4 to be returned, as these are the latest each for 28 April and 29 April respectively. Unfortunately, I have little expertise as far as SQL is concerned. Could anybody possibly provide some insight as to how to achieve this? Thanks in advance!

    Read the article

  • SQL Server Installation - What is the Installation Media Folder..??

    - by Devashri
    I am installing SQL server 2008. I have installed .Net framewok 3.5. Then I got folder SQL Server 2008.Then I performed following steps- In that I clicked configuration Tools. Then I clicked SQL Server Installation Center. I clicked "Installation" hyperlink on left side. Then I clicked "New SQL server stand-alone installation or add features to an existing installation" Then i got a window "Browse for SQL server Installation Media" which folder should i choose..?? OR is there any other method to install SQL Server..??

    Read the article

  • Can I connect to SQL Server using Windows Authentication from Java EE webapp?

    - by KG
    I am currently investigating how to make a connection to a SQL Server database from my Java EE web application using Windows Authentication instead of SQL Server authentication. I am running this app off of Tomcat 6.0, and am utilizing the Microsoft JDBC driver. My connection properties file looks as follows: dbDriver = com.microsoft.sqlserver.jdbc.SQLServerDriver dbUser = user dbPass = password dbServer = localhost:1433;databaseName=testDb dbUrl = jdbc:sqlserver://localhost:1433 I have zero problems with connecting to a SQL Server database in this fashion when using SQL Server authentication. Is there any way I can retrieve the credentials of the user's Windows Authentication and use that authentication for SQL Server? UPDATE: I know in ASP.net there is a way to set up Windows Authentication for access to the webapp, which is exactly what I am looking for, except I want to pass that token off to SQL Server for access to the database.

    Read the article

  • When to use an MS SQL instance vs. different database on same instance

    - by BoxerBucks
    We have some MS SQL servers that are setup with different instances on the same server to separate applciation DB's as well as some servers that are setup with all DB's on the same instance, just separated with security settings. When is it advisable to create a new instance for SQL server and install your DB's in that instance as opposed to just creating a new DB on the same instance and putting security around the database itself? Is there more to the decision that just a security aspect?

    Read the article

  • Oracle????????????????????????~????????????????????

    - by Yusuke.Yamamoto
    RDBMS ???????·????????????????????????????????????????????????????????????????????????? ????????Oracle ?????????????????????????????????? Oracle Database ???????????????????????????????? ????????????????????? ????Oracle???????????????????????????????????????????????????????????????????????????? ?????????????? Oracle Database ???????????????????????? ??????????????????????????????????2????????????? 1. ??????(Query Transformation) Query Transformation ???????SQL??????????????????SQL????????????????????? Query Transformation ???Predicate Transformation ? Common Sub-expression Elimination (CSE), Order-BY Elimination (OBYE), Outer Join Elimination (OJE), Simple View Meging (SVM), Predicate Move around (PM), Complex View Merging (CVM), Sub-query Unnesting (SU), Join Predicate Push Down (JPPD) ???? OR Expansion, Star Transformation (ST) ????????????? ···???????????????????????????????????????????????????? Predicate Transformation ?????? Transitive Predicate Generation ????????????? ?????????????SQL???deptno ? 10 ????????????????????????????? select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno and e.deptno=10; ???????????????emp ??? deptno=10 ??????????????dept ??? d.deptno=10 ??????????????????? emp ?? deptno=10 ????????????????????emp ?? deptno=10 ??????10???????10? dept ????????????dept ??20???????????????????????10?*20?=200?????(??????????·?????????)? ??SQL?? Transitive Predicate Generation ??????SQL????????????????? select e.ename, d.location from emp e, dept d where e.deptno=d.deptno and e.deptno=10 and d.deptno=10; ^^^^^^^^^^^ ??????dept ?????? deptno=10 ??????????????????????????10?*1?=10(dept.deptno ?unique????)?1/20????????????????1/20????????????????10??????????30???????????????Query Transformation ???????????????????????????? ?:??????????? dept ?? 1-row table ??????dept ?? driving ???(Outer Table)??? emp ?? probe ???(Inner Table)????????????1?*10?=10 ????????????????????????????????????????????????????????1/20????????????? ?????? Query Transformation ??????SQL????????????????????????????????? Transformation ??????????????????????????????????? 2. ????·????(Access Path Analysis) Access Path Analysis ??Query Transformation ??SQL????????????(Access Path)?????????(Join Method)?????(Join Order)?????????? ??????????????????(FTS)?ROWID?????????????????????????????·?????(Nested Loop Join)???????(Hash Join)????/?????(Sort Merge Join)????????????????????????????????????????????????????????????????????????? Oracle Database ????????? Query Transformation ???? Logical Optimizer?Access Path Analysis ???? Physical Optimizer ????????? ??????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????? Oracle Database ????????????????????? "Oracle ????????" ?????????? Sustaining Engineering?? ?(??? ???) ???????????????? Sustaining Engineering ????????????????????????Oracle Database ???????????????????????? ?????????????????????Ruby????????????????????????? Oracle????????????????????????! Oracle????????????? Oracle????????????????????????

    Read the article

  • Windows 7 - cancel mirror synchronisation

    - by Chris W
    I've got basic disk OS managed disk mirroring setup in Windows 7 for a couple of volumes. After a power failure the mirrors are currently resynching. These are only small volumes of data but the sync has not completed after more than 24 hours. Is there any way to stop this as it's driving me nuts? I need to get the machine back to a usable state to get some work done but it's a bit of a dog whilst this synch is going on. I've tried removing the mirrors but it won't let me do that whilst the re-sync is in progress.

    Read the article

  • Windows 7 - cancel mirror synchronisation

    - by Chris W
    I've got basic disk OS managed disk mirroring setup in Windows 7 for a couple of volumes. After a power failure the mirrors are currently resynching. These are only small volumes of data but the sync has not completed after more than 24 hours. Is there any way to stop this as it's driving me nuts? I need to get the machine back to a usable state to get some work done but it's a bit of a dog whilst this synch is going on. I've tried removing the mirrors but it won't let me do that whilst the re-sync is in progress.

    Read the article

  • Have 3 Monitors Display the same thing? (WinXP)

    - by Drew Tenenbaum
    I have a setup in which there is a Computer running windows XP, with 3 monitors plugged into it, with a "Matrox Millennium P750" Graphics card. What I want is to enable mirroring of ALL THREE MONITORS, meaning I want all the monitors to display the same image. In Matrox Powerdesk, it does not have an option to do this, and I'm pretty sure that Windows XP does not support it nativley. I really should only need some software/drivers, right? What can i do to display the same thing on 3 screens?

    Read the article

  • Download binary file From SQL Server 2000

    - by kareemsaad
    I inserted binary files (images, PDF, videos..) and I want to retrieve this file to download it. I used generic handler page as this public void ProcessRequest (HttpContext context) { using (System.Data.SqlClient.SqlConnection con = Connection.GetConnection()) { String Sql = "Select BinaryData From ProductsDownload Where Product_Id = @Product_Id"; SqlCommand com = new SqlCommand(Sql, con); com.CommandType = System.Data.CommandType.Text; com.Parameters.Add(Parameter.NewInt("@Product_Id", context.Request.QueryString["Product_Id"].ToString())); SqlDataReader dr = com.ExecuteReader(); if (dr.Read() && dr != null) { Byte[] bytes; bytes = Encoding.UTF8.GetBytes(String.Empty); bytes = (Byte[])dr["BinaryData"]; context.Response.BinaryWrite(bytes); dr.Close(); } } } and this is my table CREATE TABLE [ProductsDownload] ( [ID] [bigint] IDENTITY (1, 1) NOT NULL , [Product_Id] [int] NULL , [Type_Id] [int] NULL , [Name] [nvarchar] (200) COLLATE Arabic_CI_AS NULL , [MIME] [varchar] (50) COLLATE Arabic_CI_AS NULL , [BinaryData] [varbinary] (4000) NULL , [Description] [nvarchar] (500) COLLATE Arabic_CI_AS NULL , [Add_Date] [datetime] NULL , CONSTRAINT [PK_ProductsDownload] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [FK_ProductsDownload_DownloadTypes] FOREIGN KEY ( [Type_Id] ) REFERENCES [DownloadTypes] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_ProductsDownload_Product] FOREIGN KEY ( [Product_Id] ) REFERENCES [Product] ( [Product_Id] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY] GO And use data list has label for file name and button to download file as <asp:DataList ID="DataList5" runat="server" DataSource='<%#GetData(Convert.ToString(Eval("Product_Id")))%>' RepeatColumns="1" RepeatLayout="Flow"> <ItemTemplate> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td class="spc_tab_hed_bg spc_hed_txt lm5 tm2 bm3"> <asp:Label ID="LblType" runat="server" Text='<%# Eval("TypeName", "{0}") %>'></asp:Label> </td> <td width="380" class="spc_tab_hed_bg"> &nbsp; </td> </tr> <tr> <td align="left" class="lm5 tm2 bm3"> <asp:Label ID="LblData" runat="server" Text='<%# Eval("Name", "{0}") %>'></asp:Label> </td> <td align="center" class=" tm2 bm3"> <a href='<%# "DownloadFile.aspx?Product_Id=" + DataBinder.Eval(Container.DataItem,"Product_Id") %>' > <img src="images/downloads_ht.jpg" width="11" height="11" border="0" /> </a> <%--<asp:ImageButton ID="ImageButton1" ImageUrl="images/downloads_ht.jpg" runat="server" OnClick="ImageButton1_Click1" />--%> </td> </tr> </table> </ItemTemplate> </asp:DataList> I tried more to solve this problem but I cannot please if any one has solve for this proplem please sent me thank you kareem saad programmer MCTS,MCPD Toshiba Company Egypt

    Read the article

  • SQL query mixing aggregated results and single values

    - by Paul Flowerdew
    I have a table with transactions. Each transaction has a transaction ID, and accounting period (AP), and a posting value (PV), as well as other fields. Some of the IDs are duplicated, usually because the transaction was done in error. To give an example, part of the table might look like: ID PV AP 123 100 2 123 -100 5 In this case the transaction was added in AP2 then removed in AP5. Another example would be: ID PV AP 456 100 2 456 -100 5 456 100 8 In the first example, the problem is that if I am analyzing what was spent in AP2, there is a transaction in there which actually shouldn't be taken into account because it was taken out again in AP5. In the second example, the second two transactions shouldn't be taken into account because they cancel each other out. I want to label as many transactions as possible which shouldn't be taken into account as erroneous. To identify these transactions, I want to find the ones with duplicate IDs whose PVs sum to zero (like ID 123 above) or transactions where the PV of the earliest one is equal to sum(PV), as in the second example. This second condition is what is causing me grief. So far I have SELECT * FROM table WHERE table.ID IN (SELECT table.ID FROM table GROUP BY table.ID HAVING COUNT(*) > 1 AND (SUM(table.PV) = 0 OR SUM(table.PV) = <PV of first transaction in each group>)) ORDER BY table.ID; The bit in chevrons is what I'm trying to do and I'm stuck. Can I do it like this or is there some other method I can use in SQL to do this? Edit 1: Btw I forgot to say that I'm using SQL Compact 3.5, in case it matters. Edit 2: I think the code snippet above is a bit misleading. I still want to mark out transactions with duplicate IDs where sum(PV) = 0, as in the first example. But where the PV of the earliest transaction = sum(PV), as in the second example, what I actually want is to keep the earliest transaction and mark out all the others with the same ID. Sorry if that caused confusion. Edit 3: I've been playing with Clodoaldo's solution and have made some progress, but still can't get quite what I want. I'm trying to get the transactions I know for certain to be erroneous. Suppose the following transactions are also in the table: ID PV AP 789 100 2 789 200 5 789 -100 8 In this example sum(PV) < 0 and the earliest PV < sum(PV) so I don't want to mark any of these out. If I modify Clodoaldo's query as follows: select t.* from t left join ( select id, min(ap) as ap, sum(pv) as sum_pv from t group by id having sum(pv) <> 0 ) s on t.id = s.id and t.ap = s.ap and t.pv = s.sum_pv where s.id is null This gives the result ID PV AP 123 100 2 123 -100 5 456 -100 5 456 100 8 789 100 3 789 200 5 789 -100 8 Whilst the first 4 transactions are ok (they would be marked out), the 789 transactions are also there, and I don't want them. But I can't figure out how to modify the query so that they're not included. Any ideas?

    Read the article

  • Trying to import SQL file in a xampp server returns error

    - by Victor_J_Martin
    I have done a ER diagram in Mysql Workbench, and I am trying load in my server with phpMyAdmin, but it returns me the next error: Error SQL Query: -- ----------------------------------------------------- -- Table `BDA`.`UG` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`UG` ( `numero_ug` INT NOT NULL, `nombre` VARCHAR(45) NOT NULL, `segunda_firma_autorizada` VARCHAR(45) NOT NULL, `fecha_creacion` DATE NOT NULL, `nombre_depto` VARCHAR(140) NOT NULL, `dni` INT NOT NULL, `anho_contable` INT NOT NULL, PRIMARY KEY (`numero_ug`), INDEX `nombre_depto_idx` (`nombre_depto` ASC), INDEX `dni_idx` (`dni` ASC), INDEX `anho_contable_idx` (`anho_contable` ASC), CONSTRAINT `nombre_depto` FOREIGN KEY (`nombre_depto`) REFERENCES `BDA`.`Departamento` (`nombre_depto`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `dni` FOREIGN KEY (`dni`) REFERENCES `BDA`.`Trabajador` (`dni`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `anho_contable` FOREIGN KEY (`anho_contable`) REFERENCES `BDA`.`Capitulo_Contable` (`anho_contable`) [...] MySQL said: Documentation #1022 - Can't write; duplicate key in table 'ug' I export the result of the diagram from Mysql Workbench to a SQL file, and this file is what I'm trying to upload. This is the file. I can not find the duplicate key. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; CREATE SCHEMA IF NOT EXISTS `BDA` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; USE `BDA` ; -- ----------------------------------------------------- -- Table `BDA`.`Departamento` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Departamento` ( `nombre_depto` VARCHAR(140) NOT NULL, `area_depto` VARCHAR(140) NOT NULL, PRIMARY KEY (`nombre_depto`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Trabajador` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Trabajador` ( `dni` INT NOT NULL, `direccion` VARCHAR(140) NOT NULL, `nombre` VARCHAR(45) NOT NULL, `apellidos` VARCHAR(140) NOT NULL, `fecha_nacimiento` DATE NOT NULL, `fecha_contrato` DATE NOT NULL, `titulacion` VARCHAR(140) NULL, `nombre_depto` VARCHAR(45) NOT NULL, PRIMARY KEY (`dni`), INDEX `nombre_depto_idx` (`nombre_depto` ASC), CONSTRAINT `nombre_depto` FOREIGN KEY (`nombre_depto`) REFERENCES `BDA`.`Departamento` (`nombre_depto`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Capitulo_Contable` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Capitulo_Contable` ( `anho_contable` INT NOT NULL, `numero_ug` INT NOT NULL, `debe` DOUBLE NOT NULL, `haber` DOUBLE NOT NULL, PRIMARY KEY (`anho_contable`), INDEX `numero_ug_idx` (`numero_ug` ASC), CONSTRAINT `numero_ug` FOREIGN KEY (`numero_ug`) REFERENCES `BDA`.`UG` (`numero_ug`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`UG` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`UG` ( `numero_ug` INT NOT NULL, `nombre` VARCHAR(45) NOT NULL, `segunda_firma_autorizada` VARCHAR(45) NOT NULL, `fecha_creacion` DATE NOT NULL, `nombre_depto` VARCHAR(140) NOT NULL, `dni` INT NOT NULL, `anho_contable` INT NOT NULL, PRIMARY KEY (`numero_ug`), INDEX `nombre_depto_idx` (`nombre_depto` ASC), INDEX `dni_idx` (`dni` ASC), INDEX `anho_contable_idx` (`anho_contable` ASC), CONSTRAINT `nombre_depto` FOREIGN KEY (`nombre_depto`) REFERENCES `BDA`.`Departamento` (`nombre_depto`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `dni` FOREIGN KEY (`dni`) REFERENCES `BDA`.`Trabajador` (`dni`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `anho_contable` FOREIGN KEY (`anho_contable`) REFERENCES `BDA`.`Capitulo_Contable` (`anho_contable`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Cliente` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Cliente` ( `cif_cliente` INT NOT NULL, `nombre_cliente` VARCHAR(140) NOT NULL, PRIMARY KEY (`cif_cliente`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Ingreso` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Ingreso` ( `id` INT NOT NULL, `concepto` VARCHAR(45) NOT NULL, `importe` DOUBLE NOT NULL, `fecha` DATE NOT NULL, `cif_cliente` INT NOT NULL, `numero_ug` INT NOT NULL, PRIMARY KEY (`id`), INDEX `cif_cliente_idx` (`cif_cliente` ASC), INDEX `numero_ug_idx` (`numero_ug` ASC), CONSTRAINT `cif_cliente` FOREIGN KEY (`cif_cliente`) REFERENCES `BDA`.`Cliente` (`cif_cliente`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `numero_ug` FOREIGN KEY (`numero_ug`) REFERENCES `BDA`.`UG` (`numero_ug`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Proveedor` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Proveedor` ( `cif_proveedor` INT NOT NULL, `nombre_proveedor` VARCHAR(140) NOT NULL, PRIMARY KEY (`cif_proveedor`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Gasto` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Gasto` ( `id` INT NOT NULL, `concepto` VARCHAR(45) NOT NULL, `importe` DOUBLE NOT NULL, `fecha` DATE NOT NULL, `factura` INT NOT NULL, `cif_proveedor` INT NOT NULL, `numero_ug` INT NOT NULL, PRIMARY KEY (`id`), INDEX `cif_proveedor_idx` (`cif_proveedor` ASC), INDEX `numero_ug_idx` (`numero_ug` ASC), CONSTRAINT `cif_proveedor` FOREIGN KEY (`cif_proveedor`) REFERENCES `BDA`.`Proveedor` (`cif_proveedor`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `numero_ug` FOREIGN KEY (`numero_ug`) REFERENCES `BDA`.`UG` (`numero_ug`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Thanks for your advices.

    Read the article

  • C#: The input stream is not a valid binary format.

    - by Mcoroklo
    I have a problem with deserializing in C#/ASP.NET, which gives the exact error: The input stream is not a valid binary format. The starting contents (in bytes) are: 41-41-45-41-41-41-44-2F-2F-2F-2F-2F-41-51-41-41-41 ... What I am trying to do I have a structure with 3 classes. I have a class A which is a base class, and then class B and C which are derived from A. I am trying to store random types of B and C in the database using LINQ to SQL, in a column with the type VARCHAR(MAX). I cannot use BINARY as the length is around 15.000. My code... Error is in the LAST codeblock C# Code in Business layer- Storing a record private void AddTraceToDatabase(FightTrace trace) { MemoryStream recieverStream = new MemoryStream(); MemoryStream firedStream = new MemoryStream(); MemoryStream moveStream = new MemoryStream(); BinaryFormatter binaryFormatter = new BinaryFormatter(); binaryFormatter.Serialize(recieverStream,trace.Reciever); binaryFormatter.Serialize(firedStream,trace.FiredBy); binaryFormatter.Serialize(moveStream,trace.Move); string reciever = Convert.ToBase64String(recieverStream.ToArray()); string fired = Convert.ToBase64String(firedStream.ToArray()); string move = Convert.ToBase64String(moveStream.ToArray()); this.dataAccess.AddFightTrace(trace.TraceType.ToString(),reciever,move,fired,trace.DateTime,this.FightId); } C# Code in Data access layer - Storing a record public void AddFightTrace(string type, string reciever, string Move, string firedBy, DateTime firedAt, int fightid) { GameDataContext db = new GameDataContext(); dbFightTrace trace = new dbFightTrace(); trace.TraceType = type; trace.Reciever = reciever; trace.Move = Move; trace.FiredBy = firedBy; trace.FiredAt = firedAt; trace.FightId = fightid; db.dbFightTraces.InsertOnSubmit(trace); db.SubmitChanges(); } C# Code getting the entry in the database public List<dbFightTrace> GetNewTraces(int fightid, DateTime lastUpdate) { GameDataContext db = new GameDataContext(); var data = from d in db.dbFightTraces where d.FightId==fightid && d.FiredAt > lastUpdate select d; return data.ToList(); } C# Factory, converting from LINQ to SQL class to my objects THIS IS HERE THE ERROR COMES public FightTrace CreateTrace(dbFightTrace trace) { TraceType traceType = (TraceType) Enum.Parse(typeof(TraceType), trace.TraceType); BinaryFormatter formatter = new BinaryFormatter(); System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding(); MemoryStream recieverStream = new MemoryStream(enc.GetBytes(trace.Reciever)); recieverStream.Position = 0; MemoryStream firedStream = new MemoryStream(enc.GetBytes(trace.FiredBy)); firedStream.Position = 0; MemoryStream movedStream = new MemoryStream(enc.GetBytes(trace.Move)); movedStream.Position = 0; // THE NEXT LINE HERE CAUSES THE ERROR NPC reciever = formatter.Deserialize(recieverStream) as NPC; Player fired = formatter.Deserialize(firedStream) as Player; BaseAttack attack = formatter.Deserialize(movedStream) as BaseAttack; FightTrace t = new FightTrace(traceType,reciever,attack,fired); t.TraceId = trace.FightTraceId; t.DateTime = trace.FiredAt; return t; } So the error happends when the first Deserialize method is run, with the above error. I have tried several things but I am quite lost on this one.. Thanks! :-)

    Read the article

  • insert xml into sql server

    - by JonDog
    ok, so I know there are a bunch of other post on importing xml to sql server but I just cant seem to figure it out. I think my problem may have to do with having multi-levels. Can anyone help please. My xml, sql table, and the code i've tried. thanks in advance. <items> <item> <sku> <value> the_sku </value> </sku> <short_dis2> <value> short_discription2 <value/> </short_dis2> <short_dis1> <value> short_discription1 </value> </short_dis1> <title> <value> product_title </value> </title> <detailSpec> <value> detailed_specification_html </value> </detailSpec> <basicSpec> <value> basic_overview_html </value> </basicSpec> <basicSpecHeading> <value> besic_spec_heading </value> </basicSpecHeading> <detailSpecHeading> <value> detailed_specifications_heading </value> </detailSpecHeading> <model> <value> the_model_number </value> </model> <image_file_name> <value> the_image_url </value> </image_file_name> </item> ... CREATE TABLE [dbo].[products]( [sku] [nchar](15) NOT NULL, [model] [nvarchar](50) NULL, [title] [ntext] NULL, [short_dis1] [ntext] NULL, [short_dis2] [ntext] NULL, [basicSpecHeading] [ntext] NULL, [basicSpec] [ntext] NULL, [detailSpecHeading] [ntext] NULL, [detailSpec] [ntext] NULL, [image_file_name] [nchar](100) NULL INSERT INTO products (sku, short_dis2,short_dis1,title,detailSpec,basicSpec,basicSpecHeading,detailSpecHeading,model,image_file_name) SELECT X.product.query('sku').value('.', 'nchar(15)'), X.product.query('short_dis2').value('.', 'nvarchar(max)'), X.product.query('short_dis1').value('.', 'nvarchar(max)'), X.product.query('title').value('.', 'nvarchar(max)'), X.product.query('detailSpec').value('.', 'nvarchar(max)'), X.product.query('basicSpec').value('.', 'nvarchar(max)'), X.product.query('basicSpecHeading').value('.', 'nvarchar(max)'), X.product.query('detailSpecHeading').value('.', 'nvarchar(max)'), X.product.query('model').value('.', 'nvarchar(max)'), X.product.query('image_file_name').value('.', 'nvarchar(max)') FROM ( SELECT CAST(x AS XML) FROM OPENROWSET( BULK 'C:\users\me\desktop\xml_sample.xml', SINGLE_BLOB) AS T(x) ) AS T(x) CROSS APPLY x.nodes('Products/Product') AS X(product);

    Read the article

  • Troubleshooting Application Timeouts in SQL Server

    - by Tara Kizer
    I recently received the following email from a blog reader: "We are having an OLTP database instance, using SQL Server 2005 with little to moderate traffic (10-20 requests/min). There are also bulk imports that occur at regular intervals in this DB and the import duration ranges between 10secs to 1 min, depending on the data size. Intermittently (2-3 times in a week), we face an issue, where queries get timed out (default of 30 secs set in application). On analyzing, we found two stored procedures, having queries with multiple table joins inside them of taking a long time (5-10 mins) in getting executed, when ideally the execution duration ranges between 5-10 secs. Execution plan of the same displayed Clustered Index Scan happening instead of Clustered Index Seek. All required Indexes are found to be present and Index fragmentation is also minimal as we Rebuild Indexes regularly alongwith Updating Statistics. With no other alternate options occuring to us, we restarted SQL server and thereafter the performance was back on track. But sometimes it was still giving timeout errors for some hits and so we also restarted IIS and that stopped the problem as of now." Rather than respond directly to the blog reader, I thought it would be more interesting to share my thoughts on this issue in a blog. There are a few things that I can think of that could cause abnormal timeouts: Blocking Bad plan in cache Outdated statistics Hardware bottleneck To determine if blocking is the issue, we can easily run sp_who/sp_who2 or a query directly on sysprocesses (select * from master..sysprocesses where blocking <> 0).  If blocking is present and consistent, then you'll need to determine whether or not to kill the parent blocking process.  Killing a process will cause the transaction to rollback, so you need to proceed with caution.  Killing the parent blocking process is only a temporary solution, so you'll need to do more thorough analysis to figure out why the blocking was present.  You should look into missing indexes and perhaps consider changing the database's isolation level to READ_COMMITTED_SNAPSHOT. The blog reader mentions that the execution plan shows a clustered index scan when a clustered index seek is normal for the stored procedure.  A clustered index scan might have been chosen either because that is what is in cache already or because of out of date statistics.  The blog reader mentions that bulk imports occur at regular intervals, so outdated statistics is definitely something that could cause this issue.  The blog reader may need to update statistics after imports are done if the imports are changing a lot of data (greater than 10%).  If the statistics are good, then the query optimizer might have chosen to scan rather than seek in a previous execution because the scan was determined to be less costly due to the value of an input parameter.  If this parameter value is rare, then its execution plan in cache is what we call a bad plan.  You want the best plan in cache for the most frequent parameter values.  If a bad plan is a recurring problem on your system, then you should consider rewriting the stored procedure.  You might want to break up the code into multiple stored procedures so that each can have a different execution plan in cache. To remove a bad plan from cache, you can recompile the stored procedure.  An alternative method is to run DBCC FREEPROCACHE which drops the procedure cache.  It is better to recompile stored procedures rather than dropping the procedure cache as dropping the procedure cache affects all plans in cache rather than just the ones that were bad, so there will be a temporary performance penalty until the plans are loaded into cache again. To determine if there is a hardware bottleneck occurring such as slow I/O or high CPU utilization, you will need to run Performance Monitor on the database server.  Hopefully you already have a baseline of the server so you know what is normal and what is not.  Be on the lookout for I/O requests taking longer than 12 milliseconds and CPU utilization over 90%.  The servers that I support typically are under 30% CPU utilization, but your baseline could be higher and be within a normal range. If restarting the SQL Server service fixes the problem, then the problem was most likely due to blocking or a bad plan in the procedure cache.  Rather than restarting the SQL Server service, which causes downtime, the blog reader should instead analyze the above mentioned things.  Proceed with caution when restarting the SQL Server service as all transactions that have not completed will be rolled back at startup.  This crash recovery process could take longer than normal if there was a long-running transaction running when the service was stopped.  Until the crash recovery process is completed on the database, it is unavailable to your applications. If restarting IIS fixes the problem, then the problem might not have been inside SQL Server.  Prior to taking this step, you should do analysis of the above mentioned things. If you can think of other reasons why the blog reader is facing this issue a few times a week, I'd love to hear your thoughts via a blog comment.

    Read the article

  • Workaround for datadude deployment bug - NullReferenceException

    - by jamiet
    I have come across a bug in Visual Studio 2010 Database Projects (aka datadude aka DPro aka Visual Studio Database Development Tools aka Visual Studio Team Edition for Database Professionals aka Juneau aka SQL Server Data Tools) that other people may encounter so, for the purposes of googling, I'm writing this blog post about it. Through my own googling I discovered that a Connect bug had already been raised about it (VS2010 Database project deploy - “SqlDeployTask” task failed unexpectedly, NullReferenceException), and coincidentally enough it was raised by my former colleague Tom Hunter (whom I have mentioned here before as the superhuman Tom Hunter) although it has not (at this time) received a reply from Microsoft. Tom provided a repro, namely that this syntactically valid function definition: CREATE FUNCTION [dbo].[Function1]()RETURNS TABLEASRETURN (    WITH cte AS (    SELECT 1 AS [c1]    FROM [$(Database3)].[dbo].[Table1]   )   SELECT 1 AS [c1]   FROM cte) would produce this nasty unhelpful error upon deployment: C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets(120,5): Error MSB4018: The "SqlDeployTask" task failed unexpectedly.System.NullReferenceException: Object reference not set to an instance of an object.   at Microsoft.Data.Schema.Sql.SchemaModel.SqlModelComparerBase.VariableSubstitution(SqlScriptProperty propertyValue, IDictionary`2 variables, Boolean& isChanged)   at Microsoft.Data.Schema.Sql.SchemaModel.SqlModelComparerBase.ArePropertiesEqual(IModelElement source, IModelElement target, ModelPropertyClass propertyClass, ModelComparerConfiguration configuration)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareProperties(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonChangeDefinition changes)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareChildren(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareParentElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes, Boolean isComposing)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareChildren(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareParentElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes, Boolean isComposing)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareAllElementsForOneType(ModelElementClass type, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean compareOrphanedElements)   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareStore(ModelStore source, ModelStore target, ModelComparerConfiguration configuration)   at Microsoft.Data.Schema.Build.SchemaDeployment.CompareModels()   at Microsoft.Data.Schema.Build.SchemaDeployment.PrepareBuildPlan()   at Microsoft.Data.Schema.Build.SchemaDeployment.Execute(Boolean executeDeployment)   at Microsoft.Data.Schema.Build.SchemaDeployment.Execute()   at Microsoft.Data.Schema.Tasks.DBDeployTask.Execute()   at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()   at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggingContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean& taskResult)   Done executing task "SqlDeployTask" -- FAILED.  Done building target "DspDeploy" in project "Lloyds.UKTax.DB.UKtax.dbproj" -- FAILED. Done executing task "CallTarget" -- FAILED.Done building target "DBDeploy" in project It turns out there are a certain set of circumstances that need to be met for this error to occur: The object being deployed is an inline function  (may also exist for multistatement and scalar functions - I haven't tested that) That object includes SQLCMD variable references The object has already been deployed successfully Just to reiterate that last bullet point, the error does not occur when you deploy the function for the first time, only on the subsequent deployment.   Luckily I have a direct line into a guy on the development team so I fired off an email on Friday evening and today (Monday) I received a reply back telling me that there is a simple fix, one simply has to remove the parentheses that wrap the SQL statement. So, in the case of Tom's repro, the function definition simpy has to be changed to: CREATE FUNCTION [dbo].[Function1]()RETURNS TABLEASRETURN --(    WITH cte AS (    SELECT 1 AS [c1]    FROM [$(Database3)].[dbo].[Table1]   )   SELECT 1 AS [c1]   FROM cte--) I have commented out the offending parentheses rather than removing them just to emphasize the point. Thereafter the function will deploy fine. I tested this out on my own project this morning and can confirm that this fix does indeed work.   I have been told that the bug CAN be reproduced in the Release Candidate (RC) 0 build of SQL Server Data Tools in SQL Server 2010 so am hoping that a fix makes it in for the Release-To-Manufacturing (RTM) build. Hope this helps @jamiet

    Read the article

  • Ted Kummert to make significant announcement related to SQL Server

    - by jamiet
    Microsoft have announced a conference call tomorrow with the head of all things SQL Server, Ted Kummert: Normally I wouldn’t take any notice of such things but the mysterious pre-conference-call-announcement (not something that the SQL Server team do regularly as I recall) has me intrigued. Logic says that it will have something to do with SQL Server R2, we shall see! @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • SQL Server 2012 AlwaysOn Groups and FCIs Part 4

    This is Part 4 of a series on AlwaysOn and FCI integration in SQL Server. In this article we will learn how to add the iSCSI disk storage to our SQL Server nodes and build the cluster. 24% of devs don’t use database source control – make sure you aren’t one of themVersion control is standard for application code, but databases haven’t caught up. So what steps can you take to put your SQL databases under version control? Why should you start doing it? Read more to find out…

    Read the article

  • Buck Woody in Adelaide via LiveMeeting

    - by Rob Farley
    The URL for attendees is https://www.livemeeting.com/cc/usergroups/join?id=ADL1005&role=attend . This meeting is with Buck Woody . If you don’t know who he is, then you ought to find out! He’s a Program Manager at Microsoft on the SQL Server team, and anything else I try to say about him will not do him justice. So it’s great to have him present to the Adelaide SQL Server User Group this week. The talk is on the topic of Data-Tier Applications (new in SQL 2008 R2), and I’m sure it will be a great...(read more)

    Read the article

  • Buck Woody in Adelaide via LiveMeeting

    - by Rob Farley
    The URL for attendees is https://www.livemeeting.com/cc/usergroups/join?id=ADL1005&role=attend . This meeting is with Buck Woody . If you don’t know who he is, then you ought to find out! He’s a Program Manager at Microsoft on the SQL Server team, and anything else I try to say about him will not do him justice. So it’s great to have him present to the Adelaide SQL Server User Group this week. The talk is on the topic of Data-Tier Applications (new in SQL 2008 R2), and I’m sure it will be a great...(read more)

    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 | 380 381 382 383 384 385 386 387 388 389 390 391  | Next Page >