Search Results

Search found 42321 results on 1693 pages for 'sql reporting services 05'.

Page 347/1693 | < Previous Page | 343 344 345 346 347 348 349 350 351 352 353 354  | Next Page >

• how to join tables sql server

  - by Rick

  Im having some trouble with joining two tables. This is what my two tables look like: Table 1 Customer_ID CustomerName Add. 1000 John Smith 1001 Mike Coles 1002 Sam Carter Table 2 Sensor_ID Location Temp CustIDFK 1000 NY 70 1002 NY 70 1000 ... ... 1001 1001 1002 Desired: Sensor_ID Location Temp CustIDFK 1000 NY 70 John Smith 1002 NY 70 Sam Carter 1000 ... ... John Smith 1001 Mike Coles 1001 1002 I have made Customer_ID from table 1 my primary key, created custIDFK in table 2 and set that as my foreign key. I am really new to sql server so I am still having trouble with the whole relationship piece of it. My goal is to match one customer_ID with one Sensor_ID. The problem is that the table 2 does not have "unique IDs" since they repeat so I cant set that to my foreign key. I know I will have to do either an inner join or outer join, I just dont know how to link the sensor id with customer one. I was thinking of giving my sensor_ID a unique ID but the data that is being inserted into table 2 is coming from another program. Any suggestions?

  Read the article

• Entity Framework 4.0 POCO Classes and Data Services

  If you've flipped on the POCO (Plain Ol' CLR Objects) code generation T4 templates for Entity Framework to enable testing or just 'cuz you like the code better, you might find that you lack the ability to expose that same model via Data Services as OData (Open Data). If you surf to the feed, you'll likely see something like this: The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later....Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

  Read the article

• Using variables inside macros in SQL

  - by Tim

  Hello I'm wanting to use variables inside my macro SQL on Teradata. I thought I could do something like the following: REPLACE MACRO DbName.MyMacro ( MacroNm VARCHAR(50) ) AS ( /* Variable to store last time the macro was run */ DECLARE V_LAST_RUN_DATE TIMESTAMP; /* Get last run date and store in V_LAST_RUN_DATE */ SELECT LastDate INTO V_LAST_RUN_DATE FROM DbName.RunLog WHERE MacroNm = :MacroNm; /* Update the last run date to now and save the old date in history */ EXECUTE MACRO DbName.RunLogUpdater( :MacroNm ,V_LAST_RUN_DATE ,CURRENT_TIMESTAMP ); ); However, that didn't work, so I thought of this instead: REPLACE MACRO DbName.MyMacro ( MacroNm VARCHAR(50) ) AS ( /* Variable to store last time the macro was run */ CREATE VOLATILE TABLE MacroVars AS ( SELECT LastDate AS V_LAST_RUN_DATE FROM DbName.RunLog WHERE MacroNm = :MacroNm; ) WITH DATA ON COMMIT PRESERVE ROWS; /* Update the last run date to now and save the old date in history */ EXECUTE MACRO DbName.RunLogUpdater( :MacroNm ,SELECT V_LAST_RUN_DATE FROM MacroVars ,CURRENT_TIMESTAMP ); ); I can do what I'm looking for with a Stored Procedure, however I want to avoid for performance. Do you have any ideas about this? Is there anything else I can try? Cheers Tim

  Read the article

• The Many-to-Many Revolution 2.0 #ssas #mdx #dax #m2m

  - by Marco Russo (SQLBI)

  In September 2006 I had announced in this blog the release of the first version of The Many-to-Many Revolution, a whitepaper that describes how to leverage the many-to-many dimension relationships feature that had being available since Analysis Services 2005. The paper contains many generic patterns that can be applied in many common data analysis’ scenarios. More than 5 years later and more then 20.000 unique people that downloaded the 1.0 paper, I am proud to announce that we released The Many-to-Many...(read more)

  Read the article

• SQL Server: Clutering by timestamp; pros/cons

  - by Ian Boyd

  i have a table in SQL Server, where i want inserts to be added to the end of the table (as opposed to a clustering key that would cause them to be inserted in the middle). This means i want the table clustered by some column that will constantly increase. This could be achieved by clustering on a datetime column: CREATE TABLE Things ( ... CreatedDate datetime DEFAULT getdate(), [timestamp] timestamp, CONSTRAINT [IX_Things] UNIQUE CLUSTERED (CreatedDate) ) But i can't guaranteed that two Things won't have the same time. So my requirements can't really be achieved by a datetime column. i could add a dummy identity int column, and cluster on that: CREATE TABLE Things ( ... RowID int IDENTITY(1,1), [timestamp] timestamp, CONSTRAINT [IX_Things] UNIQUE CLUSTERED (RowID) ) But you'll notice that my table already constains a timestamp column; a column which is guaranteed to be a monotonically increasing. This is exactly the characteristic i want for a candidate cluster key. So i cluster the table on the rowversion (aka timestamp) column: CREATE TABLE Things ( ... [timestamp] timestamp, CONSTRAINT [IX_Things] UNIQUE CLUSTERED (timestamp) ) Rather than adding a dummy identity int column (RowID) to ensure an order, i use what i already have. What i'm looking for are thoughts of why this is a bad idea; and what other ideas are better. Note: Community wiki, since the answers are subjective.

  Read the article

• SQL: script to create country, state tables

  - by pcampbell

  Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity: USA = states Australia = states + territories Canada = provinces + territories Mexico = states Brazil = states Sweden = provinces UK = counties, principalities, and perhaps more! Most times when approaching this problem, I have to scratch together a list of good countries, and the states/prov/counties of each. The app may be concerned with a few countries and not others. The process is full of pain. It typically involves one of two approaches: opening up some previous DB and creating a CREATE script based on those tables. Run that script in the context of the new system. creating a DTS package from database1 to database2, with all the DDL and data included in the transfer. My goal now is to script the creation and insert of the countries that I'd be concerned with in the app of the day. When I want to roll out Countries X/Y/Z, I'll open CountryX.sql, and load its states into the ProvState table. Question: do you have a set of scripts in your toolset to create schema and data for countries and state/province/county? If so, would you share your scripts here? (U.K. citizens, please feel free to correct me by way of a comment in the use of counties.)

  Read the article

• SQL group results as a column array

  - by Radek

  Hi guys, this is an SQL question and don't know which type of JOIN, GROUP BY etc. to use, it is for a chat program where messages are related to rooms and each day in a room is linked to a transcript etc. Basically, when outputting my transcripts, I need to show which users have chatted on that transcript. At the moment I link them through the messages like so: SELECT rooms.id, rooms.name, niceDate, room_transcripts.date, long FROM room_transcripts JOIN rooms ON room_transcripts.room=rooms.id JOIN transcript_users ON transcript_users.room=rooms.id AND transcript_users.date=room_transcripts.date JOIN users ON transcript_users.user=users.id WHERE room_transcripts.deleted=0 AND rooms.id IN (1,2) ORDER BY room_transcripts.id DESC, long ASC The result set looks like this: Array ( [0] => Array ( [id] => 2 [name] => Room 2 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Jerry Seinfeld ) [1] => Array ( [id] => 1 [name] => Room 1 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Jerry Seinfeld ) [2] => Array ( [id] => 1 [name] => Room 1 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Test Users ) ) I would like though for each element in the array to represent one transcript entry and for the users to be grouped in an array as the entry's element. So 'long' will be an array listing all the names. Can this be done? At the moment I just append the names and when the transcript date and room changes I echo them retrospectively, but I will do the same for files and highlighted messages and it's messy. Thanks.

  Read the article

• DB Enterprise User Security Integration With Directory Services

  - by Etienne Remillon

  Gain a better understanding of how to integrate Enterprise User Security (EUS) with various Directories by attending this 1 hour Advisor Webcast!  When: July 11, 2012 at 16:00 UK / 17:00 CET / 08:00 am Pacific / 9:00 am Mountain / 11:00 am Eastern Enterprise User Security (EUS) is a DB feature to externalize, and centrally manage DB users in a directory server. The webcast will briefly introduce EUS, followed by a detailed discussion about the various directory options that are supported, including integration with Microsoft Active Directory. We'll conclude how to avoid common pitfalls deploying EUS with directory services. TOPICS WILL INCLUDE: - Understand EUS basics - Understand EUS and directory integration options - Avoid common EUS deployment mistakes Make sure to register and mark this date on your calendar! - Details and registration.

  Read the article

• How do i pass arbitary date format from C# to sql backend

  - by Jims

  I have a datetime field for the transaction date in the back end. So I am passing that date from front C#.net, in the below format: 2011-01-01 12:17:51.967 to do this I have written: presentation layer: string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture); PropertyClass prp=new PropertyClass(); Prp.TransDate=Convert.ToDateTime(date); PropertyClass structure: Public class property { private DateTime transdate; public DateTime TransDate { get { return transdate; } set { transdate = value; } } } From DAL layer passing the TransactionDate like this: Cmd.Parameters.AddWithValue("@TranSactionDate”, SqlDbType.DateTime).value=propertyobj.TransDate; While debugging from presntation layer: string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture); in this I am getting correct expected date format, but when debugs goes to this line Prp.TransDate=Convert.ToDateTime(date); again date format changing to 1/1/2011. But my backend sql datefield wants the date paramter 2011-01-01 12:17:51.967 in this format otherwise throwing exception invalid date format. Note: While passing date as string without converting to datetime getting exceptions like: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) at System.Data.SqlTypes.SqlDateTime..ctor(DateTime value) at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)

  Read the article

• Is the Unicode prefix N still needed in SQL Compact Edition?

  - by Dave

  At least in previous versions of SQL Server, you had to prefix Unicode string constants with an "N" to make them be treated as Unicode. Thus, select foo from bar where fizz = N'buzz' (See "Server-Side Programming with Unicode" for SQL Server 2005 "from the horse's mouth" documentation.) We have an application that is using SQL Compact Edition and I am wondering if that is still necessary. From the testing I am doing, it appears to be unneeded. That is, the following SQL statements both behave identically in SQL CE, but the second one fails in SQL Server 2005: select foo from bar where foo=N'???' select foo from bar where foo='???' (I hope I'm not swearing in some language I don't know about...) I'm wondering if that is because all strings are treated as Unicode in SQL CE, or if perhaps the default code page is now Unicode-aware. If anyone has seen any official documentation, either yea or nay, I'd appreciate it. I know I could go the safe route and just add the "N"'s, but there's a lot of code that will need changed, and if I don't need to, I don't want to! Thanks for your help!

  Read the article

• How to get XML element/attribute name in SQL Server 2005

  - by OG Dude

  Hi, I have a simple procedure in SQL Server 2005 which takes some XML as input. The element attributes correspond to field names in tables. I'd like to be able to determine <elementName>, <attribNameX> dynamically as to avoid having to hardcode them into the procedure. How can I do this? The XML looks like this: <ROOT> <elementName attribName1 = "xxx" attribName2 = "yyy"/> <elementName attribName1 = "aaa" attribName2 = "bbb"/> ... </ROOT> The stored procedure like this: CREATE PROC dbo.myProc ( @XMLInput varchar(1000) ) AS BEGIN SET NOCOUNT ON DECLARE @XMLDocHandle int EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLInput SELECT someTable.someCol FROM dbo.someTable JOIN OPENXML (@XMLDocHandle, '/ROOT/elementName',1) WITH (attrib1Name int, attrib2Name int) AS XMLData ON someTable.attribName1 = XMLData.attribName1 AND someTable.attribName2 = XMLData.attribName2 EXEC sp_xml_removedocument @XMLDocHandle END GO The question has been asked here before but maybe there is a cleaner solution. Additionally, I'd like to pass the tablename as a parameter as well - I read some stuff arguing that this is bad style - so what would be a good solution for having a dynamic tablename? Thanks a lot in advance, /David

  Read the article

• OPN Developer Services for Solaris Developers

  - by user13333379

  Independent Software Vendors (ISVs) who develop applications for Solaris 11 can exploit a number of interesting services as long as they are OPN Members with a Gold (or above) status and a Solaris Knowledge specialization: Free access to a Solaris development cloud with preconfigured Solaris developer zones through the apply for the: Oracle Exastack Remote Labs to get free access to Solaris development environments for SPARC and x86. Free access to patches and support information through MOS for Oracle Solaris, Oracle Solaris Studio, Oracle Solaris Cluster including updates for development systems  apply for the Oracle Solaris Development Initiative. Free email developer support for all questions around Oracle Solaris, Oracle Solaris Studio, Oracle Solaris Cluster and Oracle technologies integrating with Solaris 11 apply for the Solaris Adoption Technical Assistance.  

  Read the article

• MS Access to sql server searching

  - by malou17

  How to use this code if we are going to use sql server database becaUSE in this code we used MS Access as the database private void btnSearch_Click(object sender, System.EventArgs e) { String pcode = txtPcode.Text; int ctr = productsDS1.Tables[0].Rows.Count; int x; bool found = false; for (x = 0; x<ctr; x++) { if (productsDS1.Tables[0].Rows[x][0].ToString() == pcode) { found = true; break; } } if (found == true) { txtPcode.Text = productsDS1.Tables[0].Rows[x][0].ToString(); txtDesc.Text = productsDS1.Tables[0].Rows[x][1].ToString(); txtPrice.Text = productsDS1.Tables[0].Rows[x][2].ToString(); } else { MessageBox.Show("Record Not Found"); } private void btnNew_Click(object sender, System.EventArgs e) { int cnt = productsDS1.Tables[0].Rows.Count; string lastrec = productsDS1.Tables[0].Rows[cnt][0].ToString(); int newpcode = int.Parse(lastrec) + 1; txtPcode.Text = newpcode.ToString(); txtDesc.Clear(); txtPrice.Clear(); txtDesc.Focus(); here's the connectionstring Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=0;Data Source="J:\2009-2010\1st sem\VC#\Sample\WindowsApplication_Products\PointOfSales.mdb"

  Read the article

• #SSAS #Tabular Workshop and Community Events in Netherlands and Denmark

  - by Marco Russo (SQLBI)

  Next week I will finally start the roadshow of the SSAS Tabular Workshop, a 2-day seminar about the new BISM Tabular model for Analysis Services that has been introduced in SQL Server 2012. During these roadshows, we always try to arrange some speeches at local community events in the evening - we already defined for Copenhagen, we have some logistic issue in Amsterdam that we're trying to solve. Here is the timetable: Netherlands SSAS Workshop in Amsterdam, NL – April 16-17, 2012 2-day seminar, I and Alberto will be the trainers for this event, register here We're trying to manage a Community event but we still don't have a confirmation, stay tuned        Denmark SSAS Workshop in Copenhagen, DK – April 26-27, 2012 2-day seminar, I and Alberto will be the trainers for this event, register here Community event on April 26, 2012 This event will run in Hellerup, at Microsoft venue All details available here: http://msbip.dk/events/26/msbip-mode-nr-5/ People from Sweden are welcome! Just register to this private group on LinkedIn in order to announce your presence, so we’ll know how many people will attend In community events we’ll deliver two speeches – here are the descriptions: Inside xVelocity (VertiPaq) PowerPivot and BISM Tabular models in Analysis Services share a great columnar-based database engine called xVelocity in-memory analytics engine (VertiPaq). If you want to improve performance and optimize memory used, you have to understand some basic principles about how this engine works, how data is compressed, and how you can design a data model for better optimization. Prepare yourself to change your mind. xVelocity optimization techniques might seem counterintuitive and are absolutely different than OLAP and SQL ones! Choosing between Tabular and Multidimensional You have a new project and you have to make an important decision upfront. Should you use Tabular or Multidimensional? It is not easy to answer, because sometime there is a clear choice, but most of the times both decisions might be correct, at least at the beginning. In this session we’ll help you making an informed decision, correctly evaluating pros and cons of each one according to common scenarios, considering both short-term and long-term consequences of your choice. I hope to meet many people in this first dates. We have many other events coming in May and June, including an online event (for US time zones), and you can also attend our PreCon Day at TechEd US in Orland (PRC06) or TechEd Europe in Amsterdam. I’ll be a good customer for airline companies in the next three months! I’m just sorry that I hadn’t time to write other articles in the last month, but I’m accumulating material that I will need to write down during some flight – stay tuned…

  Read the article

• How to prevent linq-to-sql designer undo my changing

  - by anonim.developer

  Dear All, Thanks for your attention in advance, I’ve met an issue with LINQ-2-SQL designer in VS 2008 SP1 which has made me CRAZY. I use Linq2sql as my DAL. It seems Linq2sql speeds up coding in the first step but lots of issues arise in feature specifically with table or object inheritance. In this case I have a class Entity that all other entity classes generated by Linq2sql designer inherit from. public abstract class Entity { public virtual Guid ID { get; protected set; } } public partial class User : monius.Data.Entity { } And the following generated by L2S designer (DataModel.designer.cs) [Column(Storage = "_ID", AutoSync = AutoSync.OnInsert, DbType = "UniqueIdentifier NOT NULL", IsPrimaryKey = true, IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)] [DataMember(Order = 1)] public System.Guid ID { get { return this._ID; } set { if ((this._ID != value)) { this.OnIDChanging(value); this.SendPropertyChanging(); this._ID = value; this.SendPropertyChanged("ID"); this.OnIDChanged(); } } } When I compile the code VS warns me that Warning 1 'User.ID' hides inherited member 'Entity.ID'. To make the current member override that mplementation, add the override keyword. Otherwise add the new keyword. That warning is obvious and I have to change the code generated by L2S designer (DataModel.designer.cs) to […] public override System.Guid ID { … protected set … } And the code compiled with no error or warning and everyone is happy. But that is not the end of story. As soon as I made changes to entities of the diagram (dbml) or even I open dbml file to view it, any change manually I made to designer has been vanished and POOF! Redo AGAIN. That is a painful job. Now I wonder if there is a way to force L2S designer not changing portions of auto-generated code. I’ll be appreciated if someone kindly helps me with this issue.

  Read the article

• Generating Random Records Date Wise

  - by Julian

  I work for a non-profit organization where we send volunteers to aided schools everyday. I am creating a site to display this info and am using SQL server express. I want some help regarding a query so here's my first post We have 15 volunteers currently who will go to 4 different schools to teach. Here are some conditions: We have to create a 'new' group comprising of 1 Leader and 4 TeamSupporters 'every day' except Sunday who will go to teach everyday If a person becomes a Leader in a week, he cannot become a leader again for the same week. A leader can become a TeamSupporter in the same week. Moving ahead, we can have more number of school to target, so 4 is not a constant Here's how the output should look like School1 School2 School3 School4 Jun14 Leader V6 V6 V6 V6 Support1 V3 V3 V3 V3 Support2 V9 V9 V9 V9 Support3 V12 V12 V12 V12 Support4 V1 V1 V1 V1 Jun15 Leader V2 V2 V2 V2 Support1 V7 V7 V7 V7 Support2 V9 V9 V9 V9 Support3 V8 V8 V8 V8 Support4 V11 V11 V11 V11 Jun16 Leader V9 V9 V9 V9 Support1 V6 V6 V6 V6 Support2 V4 V4 V4 V4 Support3 V3 V3 V3 V3 Support4 V14 V14 V14 V14 and so on..

  Read the article

• Increment non unique field during SQL insert

  - by phill

  I'm not sure how to word this cause I am a little confused at the moment, so bare with me while I attempt to explain, I have a table with the following fields: OrderLineID, OrderID, OrderLine, and a few other unimportant ones. OrderLineID is the primary key and is always unique(which isn't a problem), OrderID is a foreign key that isn't unique(also not a problem), and OrderLine is a value that is not unique in the table, but should be unique for any OrderIDs that are the same...so if that didn't make sense, perhaps a picture OrderLineID, OrderID, OrderLine 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 For all OrderIDs there is a unique OrderLine. I am trying to create an insert statement that gets the max OrderLine value for a specific OrderId so I can increment it, but it's not working so well and I could use a little help. What I have right now is below, I build the sql statement in a program and replace OrderID # with an actual value. I am pretty sure the problem is with the nested select statement, and incrementing the result, but I can't find any examples that do this since my google skills are weak apparently.... INSERT INTO tblOrderLine (OrderID, OrderLine) VALUES (<OrderID #>, (SELECT MAX(OrderLine) FROM tblOrderLine WHERE orderID = <same OrderID #>)+1) any help would be nice.

  Read the article

• Trying to insert a row using stored procedured with a parameter binded to an expression.

  - by Arvind Singh

  Environment: asp.net 3.5 (C# and VB) , Ms-sql server 2005 express Tables Table:tableUser ID (primary key) username Table:userSchedule ID (primary key) thecreator (foreign key = tableUser.ID) other fields I have created a procedure that accepts a parameter username and gets the userid and inserts a row in Table:userSchedule Problem: Using stored procedure with datalist control to only fetch data from the database by passing the current username using statement below works fine protected void SqlDataSourceGetUserID_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@CurrentUserName"].Value = Context.User.Identity.Name; } But while inserting using DetailsView it shows error Procedure or function OASNewSchedule has too many arguments specified. I did use protected void SqlDataSourceCreateNewSchedule_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@CreatedBy"].Value = Context.User.Identity.Name; } DetailsView properties: autogen fields: off, default mode: insert, it shows all the fields that may not be expected by the procedure like ID (primary key) not required in procedure and CreatedBy (user id ) field . So I tried removing the 2 fields from detailsview and shows error Cannot insert the value NULL into column 'CreatedBy', table 'D:\OAS\OAS\APP_DATA\ASPNETDB.MDF.dbo.OASTest'; column does not allow nulls. INSERT fails. The statement has been terminated. For some reason parameters value is not being set. Can anybody bother to understand this and help?

  Read the article

• How can I order by the result of a recursive SQL query

  - by Tony

  I have the following method I need to ORDER BY: def has_attachments? attachments.size > 0 || (!parent.nil? && parent.has_attachments?) end I have gotten this far: ORDER BY CASE WHEN attachments.size > 0 THEN 1 ELSE (CASE WHEN parent_id IS NULL THEN 0 ELSE (CASE message.parent ...what goes here ) END END END I may be looking at this wrong because I don't have experience with recursive SQL. Essentially I want to ORDER by whether a message or any of its parents has attachments. If it's attachment size is 0, I can stop and return a 1. If the message has an attachment size of 0, I now check to see if it has a parent. If it has no parent then there is no attachment, however if it does have a parent then I essentially have to do the same query case logic for the parent. UPDATE The table looks like this +---------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message_type_id | int(11) | NO | MUL | | | | message_priority_id | int(11) | NO | MUL | | | | message_status_id | int(11) | NO | MUL | | | | message_subject_id | int(11) | NO | MUL | | | | from_user_id | int(11) | YES | MUL | NULL | | | parent_id | int(11) | YES | MUL | NULL | | | expires_at | datetime | YES | MUL | NULL | | | subject_other | varchar(255) | YES | | NULL | | | body | text | YES | | NULL | | | created_at | datetime | NO | MUL | | | | updated_at | datetime | NO | | | | | lock_version | int(11) | NO | | 0 | | +---------------------+--------------+------+-----+---------+----------------+ Where the parent_id refers to the parent message, if it exists. Thanks!

  Read the article

• Linear regression confidence intervals in SQL

  - by Matt Howells

  I'm using some fairly straight-forward SQL code to calculate the coefficients of regression (intercept and slope) of some (x,y) data points, using least-squares. This gives me a nice best-fit line through the data. However we would like to be able to see the 95% and 5% confidence intervals for the line of best-fit (the curves below). What these mean is that the true line has 95% probability of being below the upper curve and 95% probability of being above the lower curve. How can I calculate these curves? I have already read wikipedia etc. and done some googling but I haven't found understandable mathematical equations to be able to calculate this. Edit: here is the essence of what I have right now. --sample data create table #lr (x real not null, y real not null) insert into #lr values (0,1) insert into #lr values (4,9) insert into #lr values (2,5) insert into #lr values (3,7) declare @slope real declare @intercept real --calculate slope and intercept select @slope = ((count(*) * sum(x*y)) - (sum(x)*sum(y)))/ ((count(*) * sum(Power(x,2)))-Power(Sum(x),2)), @intercept = avg(y) - ((count(*) * sum(x*y)) - (sum(x)*sum(y)))/ ((count(*) * sum(Power(x,2)))-Power(Sum(x),2)) * avg(x) from #lr Thank you in advance.

  Read the article

• Concatenate CLOB-rows with PL/SQL

  - by david K

  Hi, I've got a table which has an id and a clob content like: Create Table v_example_l ( nip number, xmlcontent clob ); We insert our data: Insert into V_EXAMPLE_L (NIP,XMLCONTENT) Values (17852,'<section><block><name>delta</name><content>548484646846484</content></block></section>'); Insert into V_EXAMPLE_L (NIP,XMLCONTENT) Values (17852,'<section><block><name>omega</name><content>545648468484</content></block></section>'); Insert into V_EXAMPLE_L (NIP,XMLCONTENT) Values (17852,'<section><block><name>gamma</name><content>54564846qsdqsdqsdqsd8484</content></block></section>'); I'm trying to do a function that concatenates the rows of the clob that gone be the result of a select, i mean without having to give multiple parameter about the name of table or such, i should only give here the column that contain the clobs, and it should handle the rest. CREATE OR REPLACE function assemble_clob(q varchar2) return clob is v_clob clob; tmp_lob clob; hold VARCHAR2(4000); --cursor c2 is select xmlcontent from V_EXAMPLE_L where id=17852 cur sys_refcursor; begin OPEN cur FOR q; LOOP FETCH cur INTO tmp_lob; EXIT WHEN cur%NOTFOUND; --v_clob := v_clob || XMLTYPE.getClobVal(tmp_lob.xmlcontent); v_clob := v_clob || tmp_lob; END LOOP; return (v_clob); --return (dbms_xmlquery.getXml( dbms_xmlquery.set_context("Select 1 from dual")) ) end assemble_clob; The function is broken... (if anybody could give me a help, thanks a lot, and i'm noob in sql so ....). Thanks!

  Read the article

• sql-server performance optimization by removing print statements

  - by AG

  We're going through a round of sql-server stored procedure optimizations. The one recommendation we've found that clearly applies for us is 'SET NOCOUNT ON' at the top of each procedure. (Yes, I've seen the posts that point out issues with this depending on what client objects you run the stored procedures from but these are not issues for us.) So now I'm just trying to add in a bit of common sense. If the benefit of SET NOCOUNT ON is simply to reduce network traffic by some small amount every time, wouldn't it also make sense to turn off all the PRINT statements we have in the stored procedures that we only use for debugging? I can't see how it can hurt performance. OTOH, it's a bit of a hassle to implement due to the fact that some of the print statements are the only thing within else clauses, so you can't just always comment out the one line and be done. The change carries some amount of risk so I don't want to do it if it isn't going to actually help. But I don't see eliminating print statements mentioned anywhere in articles on optimization. Is that because it is so obvious no one bothers to mention it?

  Read the article

• Passing Parameters Between Web-Services and JSF Pages

  - by shay.shmeltzer

  This is another quick demo that shows a common scenario that combines several demos I did in the past. The scenario – we have two web services, one returns a list of objects, the other allows us to update an object. We want to build a page flow where the first page shows us the list of objects, allows us to select one, and then we can edit that instance in the next page and call the second web service to update our data source. The demo shows: How to select a row and save the object value in a pageFlowScope. (using setPropertyListener). How to create a page that allows me to modify the value of the pageFlowScope object, and how to pass the object as a parameter to the second Web service. Check it out here:

  Read the article

• Parallelize incremental processing in Tabular #ssas #tabular

  - by Marco Russo (SQLBI)

  I recently came in a problem trying to improve the parallelism of Tabular processing. As you know, multiple tables can be processed in parallel, whereas the processing of several partitions within the same table cannot be parallelized. When you perform an incremental update by adding only new rows to existing table, what you really do is adding rows to a partition, so adding rows to many tables means adding rows to several partitions. The particular condition you have in this case is that every partition in which you add rows belongs to a different table. Adding rows implies using the ProcessAdd command; its QueryBinding parameter specifies a SQL syntax to read new rows, otherwise the original query specified for the partition will be used, and it could generate duplicated data if you don’t have a dynamic behavior on the SQL side. If you create the required XMLA code manually, you will find that the QueryBinding node that should be part of the ProcessAdd command has to be moved out from ProcessAdd in case you are using a Batch command with more than one Process command (which is the reason why you want to use a single batch: run multiple process operations in parallel!). If you use AMO (Analysis Management Objects) you will find that this combination is not supported, even if you don’t have a syntax error compiling the code, but you might obtain this error at execution time: The syntax for the 'Process' command is incorrect. The 'Bindings' keyword cannot appear under a 'Process' command if the 'Process' command is a part of a 'Batch' command and there are more than one 'Process' commands in the 'Batch' or the 'Batch' command contains any out of line related information. In this case, the 'Bindings' keyword should be a part of the 'Batch' command only. If this is happening to you, the best solution I’ve found is manipulating the XMLA code generated by AMO moving the Binding nodes in the right place. A more detailed description of the issue and the code required to send a correct XMLA batch to Analysis Services is available in my article Parallelize ProcessAdd with AMO. By the way, the same technique (and code) can be used also if you have the same problem in a Multidimensional model.

  Read the article

• Find out which row caused the error

  - by Felipe Fiali

  I have a big fat query that's written dynamically to integrate some data. Basically what it does is query some tables, join some other ones, treat some data, and then insert it into a final table. The problem is that there's too much data, and we can't really trust the sources, because there could be some errored or inconsistent data. For example, I've spent almost an hour looking for an error while developing using a customer's database because somewhere in the middle of my big fat query there was an error converting some varchar to datetime. It turned out to be that they had some sales dating '2009-02-29', an out-of-range date. And yes, I know. Why was that stored as varchar? Well, the source database has 3 columns for dates, 'Month', 'Day' and 'Year'. I have no idea why it's like that, but still, it is. But how the hell would I treat that, if the source is not trustable? I can't HANDLE exceptions, I really need that it comes up to another level with the original message, but I wanted to provide some more info, so that the user could at least try to solve it before calling us. So I thought about displaying to the user the row number, or some ID that would at least give him some idea of what record he'd have to correct. That's also a hard job because there will be times when the integration will run up to 80000 records. And in an 80000 records integration, a single dummy error message: 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range datetime value' means nothing at all. So any idea would be appreciated. Oh I'm using SQL Server 2005 with Service Pack 3.

  Read the article

< Previous Page | 343 344 345 346 347 348 349 350 351 352 353 354  | Next Page >