Search Results

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

Page 385/1726 | < Previous Page | 381 382 383 384 385 386 387 388 389 390 391 392  | Next Page >

  • SQL Server : copy data from one table to another

    - by Gladdy
    I want to update Table2 names with names from Table1 with matching Ids I have around 100 rows in each table. Here is my sample tables. Table1 ID Name Table2 ID Name Sample data Table1 ID |Name -------- 1 |abc 2 |bcd Table2 ID |Name -------- 1 |xyz 2 |OOS Expected result Table2 ID |Name -------- 1 |abc 2 |bcd How can I do this?

    Read the article

  • Optimising a query for Top 5% of users

    - by Nai
    On my website, there exists a group of 'power users' who are fantastic and adding lots of content on to my site. However, their prolific activities has led to their profile pages slowing down a lot. For 95% of the other users, the SPROC that is returning the data is very quick. It's only for these group of power users, the very same SPROC is slow. How does one go about optimising the query for this group of users? You can assume that the right indexes have already been constructed. EDIT: Ok, I think I have been a bit too vague. To rephrase the question, how can I optimise my site to enhance the performance for these 5% of users. Given that this SPROC is the same one that is in use for every user and that it is already well optimised, I am guessing the next steps are to explore caching possibilities on the data and application layers?

    Read the article

  • HSSFS Part 3: SQL Saturday is Awesome! And DEFAULT_DOMAIN(), and how I found it

    - by Most Valuable Yak (Rob Volk)
    Just a quick post I should've done yesterday but I was recovering from SQL Saturday #48 in Columbia, SC, where I went to some really excellent sessions by some very smart experts.  If you have not yet attended a SQL Saturday, or its been more than 1 month since you last did, SIGN UP NOW! While searching the OBJECT_DEFINITION() of SQL Server system procedures I stumbled across the DEFAULT_DOMAIN() function in xp_grantlogin and xp_revokelogin.  I couldn't find any information on it in Books Online, and it's a very simple, self-explanatory function, but it could be useful if you work in a multi-domain environment.  It's also the kind of neat thing you can find by using this query: SELECT OBJECT_SCHEMA_NAME([object_id]) object_schema, name FROM sys.all_objects WHERE OBJECT_DEFINITION([object_id]) LIKE '%()%'  ORDER BY 1,2 I'll post some elaborations and enhancements to this query in a later post, but it will get you started exploring the functional SQL Server sea. UPDATE: I goofed earlier and said SQL Saturday #46 was in Columbia. It's actually SQL Saturday #48, and SQL Saturday #46 was in Raleigh, NC.

    Read the article

  • T-SQL Tuesday #34: Help! I Need Somebody!

    - by Most Valuable Yak (Rob Volk)
    Welcome everyone to T-SQL Tuesday Episode 34!  When last we tuned in, Mike Fal (b|t) hosted Trick Shots.  These highlighted techniques or tricks that you figured out on your own which helped you understand SQL Server better. This month, I'm asking you to look back this past week, year, century, or hour...to a time when you COULDN'T figure it out.  When you were stuck on a SQL Server problem and you had to seek help. In the beginning... SQL Server has changed a lot since I started with it.  <Cranky Old Guy> Back in my day, Books Online was neither.  There were no blogs. Google was the third-place search site. There were perhaps two or three community forums where you could ask questions.  (Besides the Microsoft newsgroups...which you had to access with Usenet.  And endure the wrath of...Celko.)  Your "training" was reading a book, made from real dead trees, that you bought from your choice of brick-and-mortar bookstore. And except for your local user groups, there were no conferences, seminars, SQL Saturdays, or any online video hookups where you could interact with a person. You'd have to call Microsoft Support...on the phone...a LANDLINE phone.  And none of this "SQL Family" business!</Cranky Old Guy> Even now, with all these excellent resources available, it's still daunting for a beginner to seek help for SQL Server.  The product is roughly 1247.4523 times larger than it was 15 years ago, and it's simply impossible to know everything about it.*  So whether you are a beginner, or a seasoned pro of over a decade's experience, what do you do when you need help on SQL Server? That's so meta... In the spirit of offering help, here are some suggestions for your topic: Tell us about a person or SQL Server community who have been helpful to you.  It can be about a technical problem, or not, e.g. someone who volunteered for your local SQL Saturday.  Sing their praises!  Let the world know who they are! Do you have any tricks for using Books Online?  Do you use the locally installed product, or are you completely online with BOL/MSDN/Technet, and why? If you've been using SQL Server for over 10 years, how has your help-seeking changed? Are you using Twitter, StackOverflow, MSDN Forums, or another resource that didn't exist when you started? What made you switch? Do you spend more time helping others than seeking help? What motivates you to help, and how do you contribute? Structure your post along the lyrics to The Beatles song Help! Audio or video renditions are particularly welcome! Lyrics must include reference to SQL Server terminology or community, and performances must be in your voice or include you playing an instrument. These are just suggestions, you are free to write whatever you like.  Bonus points if you can incorporate ALL of these into a single post.  (Or you can do multiple posts, we're flexible like that.)  Help us help others by showing how others helped you! Legalese, Your Rights, Yada yada... If you would like to participate in T-SQL Tuesday please be sure to follow the rules below: Your blog post must be published between Tuesday, September 11, 2012 00:00:00 GMT and Wednesday, September 12, 2012 00:00:00 GMT. Include the T-SQL Tuesday logo (above) and hyperlink it back to this post. If you don’t see your post in trackbacks, add the link to the comments below. If you are on Twitter please tweet your blog using the #TSQL2sDay hashtag.  I can be contacted there as @sql_r, in case you have questions or problems with comments/trackback.  I'll have a follow-up post listing all the contributions as soon as I can. Thank you all for participating, and special thanks to Adam Machanic (b|t) for all his help and for continuing this series!

    Read the article

  • Problems with :uniq => true/Distinct option in a has_many_through association w/ named scope (Rails)

    - by MikeH
    I had to make some tweaks to my app to add new functionality, and my changes seem to have broken the :uniq option that was previously working perfectly. Here's the set up: #User.rb has_many :products, :through = :seasons, :uniq = true has_many :varieties, :through = :seasons, :uniq = true #product.rb has_many :seasons has_many :users, :through = :seasons, :uniq = true has_many :varieties #season.rb belongs_to :product belongs_to :variety belongs_to :user named_scope :by_product_name, :joins = :product, :order = 'products.name' #variety.rb belongs_to :product has_many :seasons has_many :users, :through = :seasons, :uniq = true First I want to show you the previous version of the view that is now breaking, so that we have a baseline to compare. The view below is pulling up products and varieties that belong to the user. In both versions below, I've assigned the same products/varieties to the user so the logs will looking at the exact same use case. #user/show <% @user.products.each do |product| %> <%= link_to product.name, product %> <% @user.varieties.find_all_by_product_id(product.id).each do |variety| %> <%=h variety.name.capitalize %></p> <% end %> <% end %> This works. It displays only one of each product, and then displays each product's varieties. In the log below, product ID 1 has 3 associated varieties. And product ID 43 has none. Here's the log output for the code above: Product Load (11.3ms) SELECT DISTINCT `products`.* FROM `products` INNER JOIN `seasons` ON `products`.id = `seasons`.product_id WHERE ((`seasons`.user_id = 1)) ORDER BY name, products.name Product Columns (1.8ms) SHOW FIELDS FROM `products` Variety Columns (1.9ms) SHOW FIELDS FROM `varieties` Variety Load (0.7ms) SELECT DISTINCT `varieties`.* FROM `varieties` INNER JOIN `seasons` ON `varieties`.id = `seasons`.variety_id WHERE (`varieties`.`product_id` = 1) AND ((`seasons`.user_id = 1)) ORDER BY name Variety Load (0.5ms) SELECT DISTINCT `varieties`.* FROM `varieties` INNER JOIN `seasons` ON `varieties`.id = `seasons`.variety_id WHERE (`varieties`.`product_id` = 43) AND ((`seasons`.user_id = 1)) ORDER BY name Ok, so everything above is the previous version which was working great. In the new version, I added some columns to the join table called seasons, and made a bunch of custom methods that query those columns. As a result, I made the following changes to the view code that you saw above so that I could access those methods on the seasons model: <% @user.seasons.by_product_name.each do |season| %> <%= link_to season.product.name, season.product %> #Note: I couldn't get this loop to work at all, so I settled for the following: #<% @user.varieties.find_all_by_product_id(product.id).each do |variety| %> <%=h season.variety.name.capitalize %> <%end%> <%end%> Here's the log output for that: SQL (0.9ms) SELECT count(DISTINCT "products".id) AS count_products_id FROM "products" INNER JOIN "seasons" ON "products".id = "seasons".product_id WHERE (("seasons".user_id = 1)) Season Load (1.8ms) SELECT "seasons".* FROM "seasons" INNER JOIN "products" ON "products".id = "seasons".product_id WHERE ("seasons".user_id = 1) AND ("seasons".user_id = 1) ORDER BY products.name Product Load (0.7ms) SELECT * FROM "products" WHERE ("products"."id" = 43) ORDER BY products.name CACHE (0.0ms) SELECT "seasons".* FROM "seasons" INNER JOIN "products" ON "products".id = "seasons".product_id WHERE ("seasons".user_id = 1) AND ("seasons".user_id = 1) ORDER BY products.name Product Load (0.4ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name Variety Load (0.4ms) SELECT * FROM "varieties" WHERE ("varieties"."id" = 2) ORDER BY name CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name Variety Load (0.4ms) SELECT * FROM "varieties" WHERE ("varieties"."id" = 8) ORDER BY name CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name Variety Load (0.4ms) SELECT * FROM "varieties" WHERE ("varieties"."id" = 7) ORDER BY name CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 43) ORDER BY products.name CACHE (0.0ms) SELECT count(DISTINCT "products".id) AS count_products_id FROM "products" INNER JOIN "seasons" ON "products".id = "seasons".product_id WHERE (("seasons".user_id = 1)) CACHE (0.0ms) SELECT "seasons".* FROM "seasons" INNER JOIN "products" ON "products".id = "seasons".product_id WHERE ("seasons".user_id = 1) AND ("seasons".user_id = 1) ORDER BY products.name CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name CACHE (0.0ms) SELECT * FROM "varieties" WHERE ("varieties"."id" = 8) ORDER BY name I'm having two problems: (1) The :uniq option is not working for products. Three distinct versions of the same product are displaying on the page. (2) The :uniq option is not working for varieties. I don't have validation set up on this yet, and if the user enters the same variety twice, it does appear on the page. In the previous working version, this was not the case. The result I need is that only one product for any given ID displays, and all varieties associated with that ID display along with such unique product. One thing that sticks out to me is the sql call in the most recent log output. It's adding 'count' to the distinct call. I'm not sure why it's doing that or whether it might be an indication of an issue. I found this unresolved lighthouse ticket that seems like it could potentially be related, but I'm not sure if it's the same issue: https://rails.lighthouseapp.com/projects/8994/tickets/2189-count-breaks-sqlite-has_many-through-association-collection-with-named-scope I've tried a million variations on this and can't get it working. Any help is much appreciated!

    Read the article

  • How to write this Linq SQL as a Dynamic Query (using strings)?

    - by Dr. Zim
    Skip to the "specific question" as needed. Some background: The scenario: I have a set of products with a "drill down" filter (Query Object) populated with DDLs. Each progressive DDL selection will further limit the product list as well as what options are left for the DDLs. For example, selecting a hammer out of tools limits the Product Sizes to only show hammer sizes. Current setup: I created a query object, sent it to a repository, and fed each option to a SQL "table valued function" where null values represent "get all products". I consider this a good effort, but far from DDD acceptable. I want to avoid any "programming" in SQL, hopefully doing everything with a repository. Comments on this topic would be appreciated. Specific question: How would I rewrite this query as a Dynamic Query? A link to something like 101 Linq Examples would be fantastic, but with a Dynamic Query scope. I really want to pass to this method the field in quotes "" for which I want a list of options and how many products have that option. from p in db.Products group p by p.ProductSize into g select new Category { PropertyType = g.Key, Count = g.Count() } Each DDL option will have "The selection (21)" where the (21) is the quantity of products that have that attribute. Upon selecting an option, all other remaining DDLs will update with the remaining options and counts. Edit: Additional notes: .OrderBy("it.City") // "it" refers to the entire record .GroupBy("City", "new(City)") // This produces a unique list of City .Select("it.Count()") //This gives a list of counts... getting closer .Select("key") // Selects a list of unique City .Select("new (key, count() as string)") // +1 to me LOL. key is a row of group .GroupBy("new (City, Manufacturer)", "City") // New = list of fields to group by .GroupBy("City", "new (Manufacturer, Size)") // Second parameter is a projection Product .Where("ProductType == @0", "Maps") .GroupBy("new(City)", "new ( null as string)")// Projection not available later? .Select("new (key.City, it.count() as string)")// GroupBy new makes key an object Product .Where("ProductType == @0", "Maps") .GroupBy("new(City)", "new ( null as string)")// Projection not available later? .Select("new (key.City, it as object)")// the it object is the result of GroupBy var a = Product .Where("ProductType == @0", "Maps") .GroupBy("@0", "it", "City") // This fails to group Product at all .Select("new ( Key, it as Product )"); // "it" is property cast though What I have learned so far is LinqPad is fantastic, but still looking for an answer. Eventually, completely random research like this will prevail I guess. LOL. Edit:

    Read the article

  • LINQ to SQL Could not find key member. Only fails on server.

    - by Adam Carr
    I have a scenario where I am inheriting from an abstract class in my partial linq to sql auto generated class implementation. My base abstract class has an abstract property called ID which I have flagged inside my LINQ to SQL model with the instance modifier override. This works fine locally without any issues. I have also done some development on another machine and it works fine there too (both in VS2008 and using Subversion). I am running CI with TeamCity and the build succeeds and deploys as desired. The problem is when the server tries to hit the database for the first time via the LINQ to SQL data context, it generates the following error. "Could not find key member 'Id' of key 'Id' on type 'CustomType'. The key may be wrong or the field or property on 'CustomType' has changed names." I have tried changing my configuration by not implementing the Id field in my base class but this still fails. Why does it work on both of my DEV machines but not on the server? I am using LINQ to SQL in another project that runs on this server just fine. FYI: LINQ to SQL, SQL 2008, .NET 3.5, SERVER 2008, IIS 7.0 UPDATE I have gone back and added the same table a second time in the same data model but without a base class and have then displayed the results from that table and got no errors. This tells me it has something to do with my base abstract class and the need to flag a property on one of my linq to sql model classes (that belongs to a key relationship) with the instance modifier of override. No answer to this yet but am getting closer. UPDATE I have fixed my issue by simply changing my approach to my problem but I am still interested in why this doesn't work. I created a new WinSrv2008 VPC and patched it, deployed a pre-built version of my site to it and still got the same error. I now assume the issue is like what the person said here, a dependency issue with VS2008. My question is what or what? Will install VS2008 on the VPC to see if it works after that.

    Read the article

  • jquery dynamic select doesn't submit values

    - by n00b0101
    I have a form that includes three select boxes. The first one is categories, and selecting a category from it will populate the variables multi-select box with values specific to the selected category. Selecting variables and then clicking "add selected" will populate the target select box will those variables. The problem is, print_r shows that the values in the target select box aren't passed upon submit, and I don't understand why... Below is the code, and help is really appreciated Here's the html markup: <select multiple="" id="categories" name="categories[]"> <option class="category" value="Income">Income</option> <option class="category" value="Gender">Gender</option> <option class="category" value="Age">Age</option> </select> //note that i'm only showing variables for a presumably select category <select multiple="multiple" id="variables" name="variables[]"> <option value="2">Less Than $15,000</option> <option value="3">$15,000 - $19,999</option> <option value="4">$20,000 - $29,999</option> <option value="5">$30,000 - $39,999</option> <option value="6">$40,000 - $49,999</option> <option value="11">$90,000 - $99,999</option> <option value="12">$100,000 - $124,999</option> <option value="13">$125,000 - $149,999</option> <option value="14">Greater than $149,999</option> </select> <select name="target[]" id="target" multiple="multiple" height="60"> </select> And here's the jquery code: $(function(){ var opts = {}, $cats = $("#categories"), $target = $("#target"), $vars = $("#variables"); $vars.find("option").each(function(){ var $opt = $(this), cat = this.className, value = this.value, label = $opt.text(); if(!opts[cat]) { opts[cat] = []; } opts[cat].push({label: label, value: value}); $opt.remove(); }); function update_variables(){ var cat = $cats.val(), new_opts = []; $vars.empty(); $.each(opts[cat], function(){ if( $target.find('[value=' + this.value + ']').length === 0 ){ new_opts.push(option(this.value, this.label)); } }); $vars.html(new_opts.join('')); } function option(value, label){ return "<option value='" + value + "'>" + label + "</option>"; } $("#add").click(function(e){ e.preventDefault(); $vars.find(':selected').appendTo($target).attr('selected',false); update_variables(); }); $("#remove").click(function(e){ e.preventDefault(); $target.find(':selected').remove(); update_variables(); }); $cats.change(function(){ update_variables(); }).change(); })

    Read the article

  • Assign table values to multiple variables using a single SELECT statement and CASE?

    - by Darth Continent
    I'm trying to assign values contained in a lookup table to multiple variables by using a single SELECT having multiple CASE statements. The table is a lookup table with two columns like so: [GreekAlphabetastic] SystemID Descriptor -------- ---------- 1 Alpha 2 Beta 3 Epsilon This is my syntax: SELECT @VariableTheFirst = CASE WHEN myField = 'Alpha' THEN tbl.SystemID END, @VariableTheSecond = CASE WHEN myField = 'Beta' THEN tbl.SystemID END, @VariableTheThird = CASE WHEN myField = 'Epsilon' THEN tbl.SystemID END FROM GreekAlphabetastic tbl However, when I check the variables after this statement executes, I expected each to be assigned the appropriate value, but instead only the last has a value assigned. SELECT @VariableTheFirst AS First, @VariableTheSecond AS Second, @VariableTheThird AS Third Results: First Second Third NULL NULL 3 What am I doing wrong?

    Read the article

  • creating a pre-menu level select screen

    - by Ephiras
    Hi I am working on creating a tower Defence java applet game and have come to a road block about implementing a title screen that i can select the level and difficulty of the rest of the game. my title screen class is called Menu. from this menu class i need to pass in many different variables into my Main class. i have used different classes before and know how to run them and such. but if both classes extend applet and each has its individual graphics method how can i run things from Main even though it was created in Menu. what i essentially want to do is run the Menu class withits action listeners and graphics until a Difficulty button has been selected, run the main class (which 100% works without having to have the Menu class) and pretty much terminate Menu so that i cannot go back to it, do not see its buttons or graphics menus. can i run one applet annd when i choose a button close that one and launch the other one? IF you would like to download the full project you can find it here, i had to comment out all the code that wasn't working my Menu class import java.awt.*; import java.awt.event.*; import java.applet.*; public class Menu extends Applet implements ActionListener{ Button bEasy,bMed,bHard; Main m; public void init(){ bEasy= new Button("Easy"); bEasy.setBounds(140,200,100,50); add(bEasy); bMed = new Button("Medium");bMed.setBounds(280,200,100,50); add(bMed); bHard= new Button("Hard");bHard.setBounds(420,200,100,50); add(bHard); setLayout(null); } public void actionPerformed(ActionEvent e){ Main m = new Main(20,10,3000,mapMed);//break; switch (e.getSource()){ case bEasy: Main m = new Main(6000,20,"levels/levelEasy.png");break;//enimies tower money world case bMed: Main m = new Main(4000,15,"levels/levelMed.png");break; case bHard: Main m = new Main(2000,10,"levels/levelEasy.png");break; default: break; } } public void paint(){ //m.draw(g) } } and here is my main class initialising code. import java.awt.*; import java.awt.event.*; import java.applet.*; import java.io.IOException; public class Main extends Applet implements Runnable, MouseListener, MouseMotionListener, ActionListener{ Button startButton, UpgRange, UpgDamage; //set up the buttons Color roadCol,startCol,finCol,selGrass,selRoad; //set up the colors Enemy e[][]; Tower t[]; Image towerpic,backpic,roadpic,levelPic; private Image i; private Graphics doubleG; //here is the world 0=grass 1=road 2=start 3=end int world[][],eStartX,eStartY; boolean drawMouse,gameEnd; static boolean start=false; static int gridLength=15; static int round=0; int Mx,My,timer=1500; static int sqrSize=31; int towers=0,towerSelected=-10; static int castleHealth=2000; String levelPath; //choose the level Easy Med or Hard int maxEnemy[] = {5,7,12,20,30,15,50,30,40,60};//number of enimies per round int maxTowers=15;//maximum number of towers allowed static int money =2000,damPrice=600,ranPrice=350,towerPrice=700; //money = the intial ammount of money you start of with //damPrice is the price to increase the damage of a tower //ranPrice is the price to increase the range of a tower public void main(int cH,int mT,int mo,int dP,int rP,int tP,String path,int[] mE)//constructor 1 castleHealth=cH; maxTowers=mT; money=mo; damPrice=dP; ranPrice=rP; towerPrice=tP; String levelPath=path; maxEnemy = mE; buildLevel(); } public void main(int cH,int mT,String path)//basic constructor castleHealth=cH; maxTowers=mT; String levelPath=path; maxEnemy = mE; buildLevel(); } public void init(){ setSize(sqrSize*15+200,sqrSize*15);//set the size of the screen roadCol = new Color(255,216,0);//set the colors for the different objects startCol = new Color(0,38,255); finCol = new Color(255,0,0); selRoad = new Color(242,204,155);//selColor is the color of something when your mouse hovers over it selGrass = new Color(0,190,0); roadpic = getImage(getDocumentBase(),"images/road.jpg"); towerpic = getImage(getDocumentBase(),"images/tower.png"); backpic = getImage(getDocumentBase(),"images/grass.jpg"); levelPic = getImage(getDocumentBase(),"images/level.jpg"); e= new Enemy[maxEnemy.length][];//activates all of the enimies for (int r=0;r<e.length;r++) e[r] = new Enemy[maxEnemy[r]]; t= new Tower[maxTowers]; for (int i=0;i<t.length;i++) t[i]= new Tower();//activates all the towers for (int i=0;i<e.length; i++)//sets all of the enimies starting co ordinates for (int j=0;j<e[i].length;j++) e[i][j] = new Enemy(eStartX,eStartY,world); initButtons();//initialise all the buttons addMouseMotionListener(this); addMouseListener(this); }

    Read the article

  • How do I pass a DBNull value to a parameterized SELECT statement?

    - by Dan
    I have a SQL statement in C# (.NET Framework 4 running against SQL Server 2k8) that looks like this: SELECT [Column1] FROM [Table1] WHERE [Column2] = @Column2 The above query works fine with the following ADO.NET code: DbParameter parm = Factory.CreateDbParameter(); parm.Value = "SomeValue"; parm.ParameterName = "@Column2"; //etc... This query returns zero rows, though, if I assign DBNull.Value to the DbParameter's Value member even if there are null values in Column2. If I change the query to accommodate the null test specifically: SELECT [Column1] FROM [Table1] WHERE [Column2] IS @Column2 I get an "Incorrect syntax near '@Column2'" exception at runtime. Is there no way that I can use null or DBNull as a parameter in the WHERE clause of a SELECT statement?

    Read the article

  • Cannot call scalar-valued CLR UDF from select ... from table statement

    - by Henrik B
    I have created a scalar-valued CLR UDF (user defined function). It takes a timezone id and a datetime and returns the datetime converted to that timezone. I can call it from a simple select without problems: "select dbo.udfConvert('Romance Standard Time', @datetime)" (@datetime is of course a valid datetime variable) But if I call it passing in a datetime from a table it fails: "select dbo.udfConvert('Romance Standard Time', StartTime) from sometable" (column StartTime is of course a column of type datetime) The error message is: "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udfConvert", or the name is ambiguous." This message is really for beginners that has misspelled something, but as it works in one case and not in the other, I don't think I have done any misspellings. Any ideas?

    Read the article

  • Is it possible to have a tableless select with multiple rows?

    - by outis
    A SELECT without a FROM clause gets us a multiple columns without querying a table: SELECT 17+23, REPLACE('bannanna', 'nn', 'n'), RAND(), CURRENT_TIMESTAMP; How can we write a query that results in multiple rows without referring to a table? Basically, abuse SELECT to turn it into a data definition statement. The result could have a single column or multiple columns. I'm most interested in a DBMS neutral answer, but others (e.g. based on UNPIVOT) are welcome. There's no technique application behind this question; it's more theoretical than practical.

    Read the article

  • how can add an extra select in this query?

    - by BulgedSnowy
    i've three tables related. images: id | filename | filesize | ... nodes: image_id | tag_id tags: id | name And i'm using this query to search images containing x tags SELECT images.* FROM images INNER JOIN nodes ON images.id = nodes.image_id WHERE tag_id IN (SELECT tags.id FROM tags WHERE tags.tag IN ("tag1","tag2")) GROUP BY images.id HAVING COUNT(*)= 2 The problem is that i need to retrieve also all images contained by the retrieved image, and i need this in the same query. This the actual query wich search retrieve all tags contained by the image: SELECT tag FROM nodes JOIN tags ON nodes.tag_id = tags.id WHERE image_id = images.id and nodes.private = images.private ORDER BY tag How can i mix this two to have only one query?

    Read the article

  • sqlcmd won't execute - Claims Native Client not installed properly

    - by nuit9
    I'm trying to use sqlcmd to execute some SQL scripts. Using a test command with a simple query like: sqlcmd -S HOSTNAME -d MYDATABASE -Q 'SELECT Names FROM Customers' sqlcmd does not appear to make any attempt to connect to the server as it displays this message: Sqlcmd: Error: Connection failure. SQL Native Client is not installed correctly. To correct this, run SQL Server Setup. The native client was presumably installed as part of the SQL Server setup and likely correctly. I actually get this message on any machine with SQL server installed trying to use sqlcmd so it's not a matter of the installation being corrupt. Unfortunately the message really tells me nothing about the problem so I don't know what the real issue is. I know the SQL Native client is working properly since a vbscript was able to execute SQL queries against the database. Is there some additional configuration needed to use sqlcmd?

    Read the article

  • Running SQL*Plus with bash causes wrong encoding

    - by Petr Mensik
    I have a problem with running SQL*Plus in the bash. Here is my code #!/bin/bash #curl http://192.168.168.165:8080/api_test/xsql/f_exp_order_1016.xsql > script.sql wget -O script.sql 192.168.168.165:8080/api_test/xsql/f_exp_order_1016.xsql set NLS_LANG=_.UTF8 sqlplus /nolog << ENDL connect login/password set sqlblanklines on start script.sql exit <<endl I download the insert statements from our intranet, put it into sql file and run it through SQL*Plus. This is working fine. My problem is that when I save the file script.sql my encoding goes wrong. All special characters(like íášc) are broken and that's causing inserting wrong characters into my DB. Encoding of that file is UTF-8, also UTF-8 is set on the XSQL page on our intranet. So I really don't know where could be a problem. And also any advices regarding to my script are welcomed, I am total newbie in Linux scripting:-)

    Read the article

  • SQLIO help decipher output

    - by SQL Learner
    When load testing on a SQL Server Box, using following (testfile is 25 GB) sqlio -kW -t8 -s360 -o8 -frandom -b8 -BH -LS g:\testfile.dat > result.txt sqlio -kW -t8 -s360 -o8 -frandom -b64 -BH -LS g:\testfile.dat >> result.txt sqlio -kW -t8 -s360 -o8 -frandom -b128 -BH -LS g:\testfile.dat >> result.txt sqlio -kW -t8 -s360 -o8 -frandom -b256 -BH -LS g:\testfile.dat >> result.txt Can anyone help me decipher output.. I do not understand latency min and average....? What does this number means IOs/sec: 10968.80 MBs/sec: 685.55 latency metrics: Min_Latency(ms): 1 Avg_Latency(ms): 5 Max_Latency(ms): 21

    Read the article

  • SQL Server 2005: Improving performance for thousands or Insert requests. logout-login time= 120ms.

    - by Rad
    Can somebody shed some lights on how SQL Server 2005 deals with may request issued by a client using ADO.NET 2.0. Below is the shortend output of SQL Trace. I can see that connection pooling is working (I believe there is only one connection being pooled). What is not clear to me is why we have so many sp_reset_connection calls i.e a series of: Audit Login, SQL:BatchStarting, RPC:Starting and Audit Logout for each loop in for loop below. I can see that there is constant switching between tempdb and master database which leads me to conclude that we lost the context when next connection is created by fetching it from the pool based on ConectionString argument. I can see that every 15ms I can get 100-200 login/logout per second (reported at the same time by Profiler). The after 15ms I have again a series fo 100-200 login/logout per second. I need clarification on how this might affect much complex insert queries in production environment. I use Enterprise Library 2006, the code is compiled with VS 2005 and it is a console application that parses a flat file with 10 of thousand of rows grouping parent-child rows, runs on an application server and runs 2 stored procedure on a remote SQL Server 2005 inserting a parent record, retrieves Identity value and using it calls the second stored procedure 1, 2 or multiple times (sometimes several thousands) inserting child records. The child table has close to 10 million records with 5-10 indexes some of them being covering non-clustered. There is a pretty complex Insert trigger that copies inserted detail record to an archive table. All in all I only have 7 inserts per second which means it can take 2-4 hours for 50 thousand records. When I run Profiler on the test server (that is almost equivalent with production server) I can see that there is about 120ms between Audit Logout and Audit Login trace entries which almost give me chance to insert about 8 records. So my question is if there is some way to improve inserting of records since the company loads 100 thousands of records and does daily planning and has SLA to fulfill client request coming as flat file orders and some big files 10 thousands have to be processed(imported quickly). 4 hours to import 60 thousands should be reduced to 30 minutes. I was thinking to use BatchSize of DataAdapter to send multiple stored procedure calls, SQL Bulk inserts to batch multiple inserts from DataReader or DataTable, SSIS fast load. But I don't know how to properly analyze re-indexing and stats population and maybe this has to take some time to finish. What is worse is that the company uses the biggest table for reporting and other online processing and indexes cannot be dropped. I manage transaction manually by setting a field to a value and do an transactional update changing that value to a new value that other applications are using to get committed rows. Please advise how to approach this problem. For now I am trying to have a staging tables with minimal logging in a separate database and no indexes and I will try to do batched (massive) parent child inserts. I believe Production DB has simple recovery model, but it could be full recovery. If DB user that is being used by my .NET console application has bulkadmin role does it mean its bulk inserts are minimally logged. I understand that when a table has clustered and many non-clustered indexes that inserts are still logged for each row. Connection pooling is working, but with many login/logouts. Why? for (int i = 1; i <= 10000; i++){ using (SqlConnection conn = new SqlConnection("server=(local);database=master;integrated security=sspi;")) {conn.Open(); using (SqlCommand cmd = conn.CreateCommand()){ cmd.CommandText = "use tempdb"; cmd.ExecuteNonQuery();}}} SQL Server Profiler trace: Audit Login master 2010-01-13 23:18:45.337 1 - Nonpooled SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.337 RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.337 Audit Logout tempdb 2010-01-13 23:18:45.337 2 - Pooled Audit Login -- network protocol master 2010-01-13 23:18:45.383 2 - Pooled SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.383 RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.383 Audit Logout tempdb 2010-01-13 23:18:45.383 2 - Pooled Audit Login -- network protocol master 2010-01-13 23:18:45.383 2 - Pooled SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.383 RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.383 Audit Logout tempdb 2010-01-13 23:18:45.383 2 - Pooled

    Read the article

  • javascript autocompletion for DSL. (e.g.: SQL)

    - by chacko
    I want to give the user a web-page where he can type some simple SQL. select * from myTable. What I would like is to have the autocompletion area to help typing: sel [selECT] select * f [select * fROM] select * from [select * fROM column1] [select * fROM column2] ... Anybody can suggest if there is a nice open source library for this ? I am not really interested in the SQL (I will have to parse a DSL) I am more interested on a powerful autocompletion text area widget.

    Read the article

  • Understanding and Controlling Parallel Query Processing in SQL Server

    Data warehousing and general reporting applications tend to be CPU intensive because they need to read and process a large number of rows. To facilitate quick data processing for queries that touch a large amount of data, Microsoft SQL Server exploits the power of multiple logical processors to provide parallel query processing operations such as parallel scans. Through extensive testing, we have learned that, for most large queries that are executed in a parallel fashion, SQL Server can deliver linear or nearly linear response time speedup as the number of logical processors increases. However, some queries in high parallelism scenarios perform suboptimally. There are also some parallelism issues that can occur in a multi-user parallel query workload. This white paper describes parallel performance problems you might encounter when you run such queries and workloads, and it explains why these issues occur. In addition, it presents how data warehouse developers can detect these issues, and how they can work around them or mitigate them.

    Read the article

  • SQL Pre-Con…at the Beach

    - by Argenis
      Building upon the success of SQL Rally 2012 (where we packed a room full of DBAs), my friend Robert Davis [Twitter|Blog] and yours truly will be again delivering our day-long Pre-Conference “Demystifying Database Administration Best Practices” this Friday (6/8/2012) – right before SQLSaturday #132 in Pensacola, FL. If you are in the vicinity of Pensacola, come join us! We had tons of fun at Rally. Robert and I love sharing tips and stories that will help you on your day to day duties as a DBA. Some of the topics that we’ll touch on (this is by no means a comprehensive list) Active Directory configuration for SQL Server Deployments Windows Server Deployments Storage and I/O High Availability / Disaster Recovery / Business Continuity Replication Day-To-Day Operations Maintenance TempDB Code Reviews Other Database and Server Settings   Follow this link to sign up for the Pre-Con at Pensacola: http://demystifyingdba.eventbrite.com/ Here’s a blog post that Robert made on the subject of Best Practices.  Hope to see you there!

    Read the article

  • SQL Pre-Con…at the Beach

    - by Argenis
      Building upon the success of SQL Rally 2012 (where we packed a room full of DBAs), my friend Robert Davis [Twitter|Blog] and yours truly will be again delivering our day-long Pre-Conference “Demystifying Database Administration Best Practices” this Friday (6/8/2012) – right before SQLSaturday #132 in Pensacola, FL. If you are in the vicinity of Pensacola, come join us! We had tons of fun at Rally. Robert and I love sharing tips and stories that will help you on your day to day duties as a DBA. Some of the topics that we’ll touch on (this is by no means a comprehensive list) Active Directory configuration for SQL Server Deployments Windows Server Deployments Storage and I/O High Availability / Disaster Recovery / Business Continuity Replication Day-To-Day Operations Maintenance TempDB Code Reviews Other Database and Server Settings   Follow this link to sign up for the Pre-Con at Pensacola: http://demystifyingdba.eventbrite.com/ Here’s a blog post that Robert made on the subject of Best Practices.  Hope to see you there!

    Read the article

  • Calculating estimated data loss with Always on

    - by blakmk
    Ever wondered how calculate estimated data loss (time) for always on. The metric in the always on dashboard shows the metric quite nicely but there does seem to be a lack of documentation about where the metrics ---come from. Heres a script that calculates the data loss ( lag ) so you can set up alerts based on your DR SLA's:       WITH DR_CTE ( replica_server_name, database_name, last_commit_time) AS                 (                                 select ar.replica_server_name, database_name, rs.last_commit_time                                 from master.sys.dm_hadr_database_replica_states  rs                                 inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id                                 inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id                                 where replica_server_name != @@servername                 ) select ar.replica_server_name, dcs.database_name, rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', datediff(ss,  DR_CTE.last_commit_time, rs.last_commit_time) 'lag_in_seconds' from master.sys.dm_hadr_database_replica_states  rs inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id inner join DR_CTE on DR_CTE.database_name = dcs.database_name where ar.replica_server_name = @@servername order by lag_in_seconds desc

    Read the article

  • Windows Azure Use Case: Hybrid Applications

    - by BuckWoody
    This is one in a series of posts on when and where to use a distributed architecture design in your organization's computing needs. You can find the main post here: http://blogs.msdn.com/b/buckwoody/archive/2011/01/18/windows-azure-and-sql-azure-use-cases.aspx  Description: Organizations see the need for computing infrastructures that they can “rent” or pay for only when they need them. They also understand the benefits of distributed computing, but do not want to create this infrastructure themselves. However, they may have considerations that prevent them from moving all of their current IT investment to a distributed environment: Private data (do not want to send or store sensitive data off-site) High dollar investment in current infrastructure Applications currently running well, but may need additional periodic capacity Current applications not designed in a stateless fashion In these situations, a “hybrid” approach works best. In fact, with Windows Azure, a hybrid approach is an optimal way to implement distributed computing even when the stipulations above do not apply. Keeping a majority of the computing function in an organization local while exploring and expanding that footprint into Windows and SQL Azure is a good migration or expansion strategy. A “hybrid” architecture merely means that part of a computing cycle is shared between two architectures. For instance, some level of computing might be done in a Windows Azure web-based application, while the data is stored locally at the organization. Implementation: There are multiple methods for implementing a hybrid architecture, in a spectrum from very little interaction from the local infrastructure to Windows or SQL Azure. The patterns fall into two broad schemas, and even these can be mixed. 1. Client-Centric Hybrid Patterns In this pattern, programs are coded such that the client system sends queries or compute requests to multiple systems. The “client” in this case might be a web-based codeset actually stored on another system (which acts as a client, the user’s device serving as the presentation layer) or a compiled program. In either case, the code on the client requestor carries the burden of defining the layout of the requests. While this pattern is often the easiest to code, it’s the most brittle. Any change in the architecture must be reflected on each client, but this can be mitigated by using a centralized system as the client such as in the web scenario. 2. System-Centric Hybrid Patterns Another approach is to create a distributed architecture by turning on-site systems into “services” that can be called from Windows Azure using the service Bus or the Access Control Services (ACS) capabilities. Code calls from a series of in-process client application. In this pattern you move the “client” interface into the server application logic. If you do not wish to change the application itself, you can “layer” the results of the code return using a product (such as Microsoft BizTalk) that exposes a Web Services Definition Language (WSDL) endpoint to Windows Azure using the Application Fabric. In effect, this is similar to creating a Service Oriented Architecture (SOA) environment, and has the advantage of de-coupling your computing architecture. If each system offers a “service” of the results of some software processing, the operating system or platform becomes immaterial, assuming it adheres to a service contract. There are important considerations when you federate a system, whether to Windows or SQL Azure or any other distributed architecture. While these considerations are consistent with coding any application for distributed computing, they are especially important for a hybrid application. Connection resiliency - Applications on-premise normally have low-latency and good connection properties, something you’re not always guaranteed in a distributed and hybrid application. Whether a centralized client or a distributed one, the code should be able to handle extended retry logic. Authorization and Access - In a single authorization environment like a Active Directory domain, security is handled at a user-password level. In a distributed computing environment, you have more options. You can mitigate this with  using The Windows Azure Application Fabric feature of ACS to make the Azure application aware of the App Fabric as an ADFS provider. However, a claims-based authentication structure is often a superior choice.  Consistency and Concurrency - When you have a Relational Database Management System (RDBMS), Consistency and Concurrency are part of the design. In a Service Architecture, you need to plan for sequential message handling and lifecycle. Resources: How to Build a Hybrid On-Premise/In Cloud Application: http://blogs.msdn.com/b/ignitionshowcase/archive/2010/11/09/how-to-build-a-hybrid-on-premise-in-cloud-application.aspx  General Architecture guidance: http://blogs.msdn.com/b/buckwoody/archive/2010/12/21/windows-azure-learning-plan-architecture.aspx   

    Read the article

< Previous Page | 381 382 383 384 385 386 387 388 389 390 391 392  | Next Page >