Search Results

Search found 98445 results on 3938 pages for 'sql server pdw'.

Page 151/3938 | < Previous Page | 147 148 149 150 151 152 153 154 155 156 157 158  | Next Page >

• Please explain this delete top 100 SQL syntax

  - by Patrick

  Basically I want to do this: delete top( 100 ) from table order by id asc but MS SQL doesn't allow order in this position The common solution seems to be this: DELETE table WHERE id IN(SELECT TOP (100) id FROM table ORDER BY id asc) But I also found this method here: delete table from (select top (100) * from table order by id asc) table which has a much better estimated execution plan (74:26). Unfortunately I don't really understand the syntax, please can some one explain it to me? Always interested in any other methods to achieve the same result as well. EDIT: I'm still not getting it I'm afraid, I want to be able to read the query as I read the first two which are practically English. The above queries to me are: delete the top 100 records from table, with the records ordered by id ascending delete the top 100 records from table where id is anyone of (this lot of ids) delete table from (this lot of records) table I can't change the third one into a logical English sentence... I guess what I'm trying to get at is how does this turn into "delete from table (this lot of records)". The 'from' seems to be in an illogical position and the second mention of 'table' is logically superfluous (to me).

  Read the article

• Date and Time Support in SQL Server 2008

  - by Aamir Hasan

    Using the New Date and Time Data Types Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} 1.       The new date and time data types in SQL Server 2008 offer increased range and precision and are ANSI SQL compatible. 2.       Separate date and time data types minimize storage space requirements for applications that need only date or time information. Moreover, the variable precision of the new time data type increases storage savings in exchange for reduced accuracy. 3.       The new data types are mostly compatible with the original date and time data types and use the same Transact-SQL functions. 4.       The datetimeoffset data type allows you to handle date and time information in global applications that use data that originates from different time zones. SELECT c.name, p.* FROM politics pJOIN country cON p.country = c.codeWHERE YEAR(Independence) < 1753ORDER BY IndependenceGO8.    Highlight the SELECT statement and click Execute ( ) to show the use of some of the date functions.T-SQLSELECT c.name AS [Country Name],        CONVERT(VARCHAR(12), p.Independence, 107) AS [Independence Date],       DATEDIFF(YEAR, p.Independence, GETDATE()) AS [Years Independent (appox)],       p.GovernmentFROM politics pJOIN country cON p.country = c.codeWHERE YEAR(Independence) < 1753ORDER BY IndependenceGO10.    Select the SET DATEFORMAT statement and click Execute ( ) to change the DATEFORMAT to day-month-year.T-SQLSET DATEFORMAT dmyGO11.    Select the DECLARE and SELECT statements and click Execute ( ) to show how the datetime and datetime2 data types interpret a date literal.T-SQLSET DATEFORMAT dmyDECLARE @dt datetime = '2008-12-05'DECLARE @dt2 datetime2 = '2008-12-05'SELECT MONTH(@dt) AS [Month-Datetime], DAY(@dt)     AS [Day-Datetime]SELECT MONTH(@dt2) AS [Month-Datetime2], DAY(@dt2)     AS [Day-Datetime2]GO12.    Highlight the DECLARE and SELECT statements and click Execute ( ) to use integer arithmetic on a datetime variable.T-SQLDECLARE @dt datetime = '2008-12-05'SELECT @dt + 1GO13.    Highlight the DECLARE and SELECT statements and click Execute ( ) to show how integer arithmetic is not allowed for datetime2 variables.T-SQLDECLARE @dt2 datetime = '2008-12-05'SELECT @dt2 + 1GO14.    Highlight the DECLARE and SELECT statements and click Execute ( ) to show how to use DATE functions to do simple arithmetic on datetime2 variables.T-SQLDECLARE @dt2 datetime2(7) = '2008-12-05'SELECT DATEADD(d, 1, @dt2)GO15.    Highlight the DECLARE and SELECT statements and click Execute ( ) to show how the GETDATE function can be used with both datetime and datetime2 data types.T-SQLDECLARE @dt datetime = GETDATE();DECLARE @dt2 datetime2(7) = GETDATE();SELECT @dt AS [GetDate-DateTime], @dt2 AS [GetDate-DateTime2]GO16.    Draw attention to the values returned for both columns and how they are equal.17.    Highlight the DECLARE and SELECT statements and click Execute ( ) to show how the SYSDATETIME function can be used with both datetime and datetime2 data types.T-SQLDECLARE @dt datetime = SYSDATETIME();DECLARE @dt2 datetime2(7) = SYSDATETIME();SELECT @dt AS [Sysdatetime-DateTime], @dt2     AS [Sysdatetime-DateTime2]GO18.    Draw attention to the values returned for both columns and how they are different.Programming Global Applications with DateTimeOffset 2.    If you have not previously created the SQLTrainingKitDB database while completing another demo in this training kit, highlight the CREATE DATABASE statement and click Execute ( ) to do so now.T-SQLCREATE DATABASE SQLTrainingKitDBGO3.    Select the USE and CREATE TABLE statements and click Execute ( ) to create table datetest in the SQLTrainingKitDB database.T-SQLUSE SQLTrainingKitDBGOCREATE TABLE datetest (  id integer IDENTITY PRIMARY KEY,  datetimecol datetimeoffset,  EnteredTZ varchar(40)); Reference:http://www.microsoft.com/downloads/details.aspx?FamilyID=E9C68E1B-1E0E-4299-B498-6AB3CA72A6D7&displaylang=en   

  Read the article

• Error while running Jetty Server on port 80 as non root user

  - by user75016

  All, I was trying to setup jetty on port 80 but its giving exception saying permission denied as below. I have setup jetty to work with setuid and configured start.ini as follows: OPTIONS=Server,jsp,jmx,resources,websocket,ext,plus,annotations,jta,jdbc,setuid (below as first configuration file in start.ini) etc/jetty-setuid.xml and jetty-setuid.xml file with username and group name of non root user. 2012-07-03 15:29:02.411:INFO:oejdp.ScanningAppProvider:Deployment monitor /opt/jetty-hightide-8.1.3.v20120416/contexts at interval 1 2012-07-03 15:29:02.454:WARN:oejuc.AbstractLifeCycle:FAILED [email protected]:80: java.net.SocketException: Permission denied java.net.SocketException: Permission denied at sun.nio.ch.Net.bind(Native Method) at sun.nio.ch.ServerSocketChannelImpl.bind(ServerSocketChannelImpl.java:126) at sun.nio.ch.ServerSocketAdaptor.bind(ServerSocketAdaptor.java:59) at org.eclipse.jetty.server.nio.SelectChannelConnector.open(SelectChannelConnector.java:182) at org.eclipse.jetty.server.AbstractConnector.doStart(AbstractConnector.java:311) at org.eclipse.jetty.server.nio.SelectChannelConnector.doStart(SelectChannelConnector.java:260) at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:59) at org.eclipse.jetty.server.Server.doStart(Server.java:273) at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:59) at org.eclipse.jetty.xml.XmlConfiguration$1.run(XmlConfiguration.java:1215) at java.security.AccessController.doPrivileged(Native Method) at org.eclipse.jetty.xml.XmlConfiguration.main(XmlConfiguration.java:1138) 2012-07-03 15:29:02.455:WARN:oejuc.AbstractLifeCycle:FAILED org.eclipse.jetty.server.Server@66da9ea4: java.net.SocketException: Permission denied java.net.SocketException: Permission denied

  Read the article

• SQL ADO.NET shortcut extensions (old school!)

  - by Jeff

  As much as I love me some ORM's (I've used LINQ to SQL quite a bit, and for the MSDN/TechNet Profile and Forums we're using NHibernate more and more), there are times when it's appropriate, and in some ways more simple, to just throw up so old school ADO.NET connections, commands, readers and such. It still feels like a pain though to new up all the stuff, make sure it's closed, blah blah blah. It's pretty much the least favorite task of writing data access code. To minimize the pain, I have a set of extension methods that I like to use that drastically reduce the code you have to write. Here they are... public static void Using(this SqlConnection connection, Action<SqlConnection> action) {     connection.Open();     action(connection);     connection.Close(); } public static SqlCommand Command(this SqlConnection connection, string sql){    var command = new SqlCommand(sql, connection);    return command;}public static SqlCommand AddParameter(this SqlCommand command, string parameterName, object value){    command.Parameters.AddWithValue(parameterName, value);    return command;}public static object ExecuteAndReturnIdentity(this SqlCommand command){    if (command.Connection == null)        throw new Exception("SqlCommand has no connection.");    command.ExecuteNonQuery();    command.Parameters.Clear();    command.CommandText = "SELECT @@IDENTITY";    var result = command.ExecuteScalar();    return result;}public static SqlDataReader ReadOne(this SqlDataReader reader, Action<SqlDataReader> action){    if (reader.Read())        action(reader);    reader.Close();    return reader;}public static SqlDataReader ReadAll(this SqlDataReader reader, Action<SqlDataReader> action){    while (reader.Read())        action(reader);    reader.Close();    return reader;} It has been awhile since I've really revisited these, so you will likely find opportunity for further optimization. The bottom line here is that you can chain together a bunch of these methods to make a much more concise database call, in terms of the code on your screen, anyway. Here are some examples: public Dictionary<string, string> Get(){    var dictionary = new Dictionary<string, string>();    _sqlHelper.GetConnection().Using(connection =>        connection.Command("SELECT Setting, [Value] FROM Settings")            .ExecuteReader()            .ReadAll(r => dictionary.Add(r.GetString(0), r.GetString(1))));    return dictionary;} or... public void ChangeName(User user, string newName){    _sqlHelper.GetConnection().Using(connection =>         connection.Command("UPDATE Users SET Name = @Name WHERE UserID = @UserID")            .AddParameter("@Name", newName)            .AddParameter("@UserID", user.UserID)            .ExecuteNonQuery());} The _sqlHelper.GetConnection() is just some other code that gets a connection object for you. You might have an even cleaner way to take that step out entirely. This looks more fluent, and the real magic sauce for me is the reader bits where you can put any kind of arbitrary method in there to iterate over the results.

  Read the article

• Can't Start ISC DHCP IPv6 Server

  - by MrDaniel

  Trying to enable the ISC DHCP server for just IPv6 on Ubuntu 12.04 LTS. I have downloaded and installed the DHCP server via the following command: $ sudo apt-get install isc-dhcp-server Then I have followed the instructions in the following resources, Ubuntu Wiki DHCPv6, SixXS - Configuring ISC DHCPv6 Server and Linux IPv6 HOWTO - Configuration of the ISC DHCP server for IPv6 . So from review all those resources it seems like I need to: set a static IPv6 address for the Interface I want to run the DHCPv6 server from that is part of the IPv6 network subnet outside the DHCP range. Edit the /etc/dhcp/dhcpd6.conf file to configure the DHCPv6 range etc. Create the /var/lib/dhcp/dhcpd6.leases Manually start the DHCPv6 server. Setting the Static IP for eth0 $ sudo ifconfig eth0 inet6 add 2001:db8:0:1::128/64 My dhcpd6.conf default-lease-time 600; max-lease-time 7200; log-facility local7; subnet6 2001:db8:0:1::/64 { #Range for clients range6 2001:db8:0:1::129 2001:db8:0:1::254; } Created the dhcpd6.leases file As indicated in the dhcpd.leases man page. $ touch /var/lib/dhcp/dhcpd6.leases #Tried with sudo as well Manually starting the DHCPv6 server. Attempted to start the server using the following command: $ sudo dhcp -6 -f -cf /etc/dhcp/dhcpd6.conf eth0 The problem, the DHCP will not start, with an append error for the dhcpd6.leases file as indicated below when running the manual start command noted above. Can't open /var/lib/dhcp/dhcpd6.leases for append. Any ideas what I might be missing?

  Read the article

• Server-side Architecture for Online Game

  - by Draiken

  basically I have a game client that has communicate with a server for almost every action it takes, the game is in Java (using LWJGL) and right now I will start making the server. The base of the game is normally one client communicating with the server alone, but I will require later on for several clients to work together for some functionalities. I've already read how authentication server should be sepparated and I intend on doing it. The problem is I am completely inexperienced in this kind of server-side programming, all I've ever programmed were JSF web applications. I imagine I'll do socket connections for pretty much every game communication since HTML is very slow, but I still don't really know where to start on my server. I would appreciate reading material or guidelines on where to start, what architecture should the game server have and maybe some suggestions on frameworks that could help me getting the client-server communication. I've looked into JNAG but I have no experience with this kind of thing, so I can't really tell if it is a solid and good messaging layer. Any help is appreciated... Thanks ! EDIT: Just a little more information about the game. It is intended to be a very complex game with several functionalities, making some functionalities a "program" inside the program. It is not an usual game, like FPS or RPG but I intend on having a lot of users using these many different "programs" inside the game. If I wasn't clear enough, I'd really appreciate people that have already developed games with java client/server architecture, how they communicated, any frameworks, apis, messaging systems, etc. It is not a question of lack of knowledge of language, more a question for advice, so I don't end up creating something that works, but in the later stages will have to be rewriten for any kind of limiting reason. PS: sorry if my english is not perfect...

  Read the article

• Problem in SQL Server 2005

  - by megala

  I Know how to create table in SQL server 2005.But due to system problem i reinstalled SQL SErver 2005.After that I select the option like that start - programs - microsoft sql server 2005 - sql server management studio express My problem is in that sql server management studio express is not exits.How to solve the above problem Thanks in advance

  Read the article

• The SQL Server Reporting Services SDK for PHP Debuts

  - by The Official Microsoft IIS Site

  Microsoft has just released the SQL Server Reporting Services SDK for PHP, which enables PHP developers to easily create reports and integrate them in their web applications. The SDK offers a simple Application Programming Interface to interoperate with SQL Server Reporting Services, Microsoft's Reporting and Business Intelligence solution. Developers will be able to use the SDK to perform common operations like listing reports in PHP applications, providing custom report parameters from a PHP...(read more)

  Read the article

• Create a Monitoring Server for SQL Server with PowerShell

  At some point you are going to need a notification system for a range of events that occur in your servers. Laerte Junior shows how you can even set up temporary or permanent alerts for any WMI events to give you a system that fits your server environment perfectly. Get Smart with SQL Backup Pro Powerful centralised management, encryption and more.SQL Backup Pro was the smartest kid at school Discover why.

  Read the article

• Auditing SQL Server 2012 Server Roles

  My organization is looking at SQL Server 2012 and I know that the ability to create roles at the server level is a new feature. Since this is new and impacts security, how do I handle them and how do I audit them? NEW! Never waste another weekend deployingDeploy SQL Server changes and ASP .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.

  Read the article

• Less reboots on Windows Server Core, is this true or just a myth?

  - by Peter Hahndorf

  Because there are less components installed on a Windows Server core OS, it needs less patches than the full OS. I read in several places that therefor it needs less reboots after patching. I'm running Server 2012 core in production since September 2012 now and I don't remember a single patch-Tuesday when I did not have to reboot the server after installing Windows updates. Are there any hard numbers out there that compare the required reboots for core vs. Full OS? Less reboots may be the main reason why people choose to go with Server core. If it actually requires just as many reboots as the full OS install, they may think again the next time they set up a server.

  Read the article

• Server's network capabilities are going into some sort of sleep mode?

  - by F4r-20

  I'm having trouble with a server (Windows Server 2008 R2) on my network. It is going down for short periods at a time, but not very often. The interesting thing: If I ping the server from my computer (we'll call this client-x) using the -t switch, I will continuously get no reply. However the second I ping client-x from the server, I can see the previous ping pick up a reply? It's almost as if pinging client-x from the server, wakes up the networking capabilities? Has anybody got any idea what is going on here?

  Read the article

• SQL Server 2005 - query with case statement

  - by user329266

  Trying to put a single query together to be used eventually in a SQL Server 2005 report. I need to: Pull in all distinct records for values in the "eventid" column for a time frame - this seems to work. For each eventid referenced above, I need to search for all instances of the same eventid to see if there is another record with TaskName like 'review1%'. Again, this seems to work. This is where things get complicated: For each record where TaskName is like review1, I need to see if another record exists with the same eventid and where TaskName='End'. Utimately, I need a count of how many records have TaskName like 'review1%', and then how many have TaskName like 'review1%' AND TaskName='End'. I would think this could be accomplished by setting a new value for each record, and for the eventid, if a record exists with TaskName='End', set to 1, and if not, set to 0. The query below seems to accomplish item #1 above: SELECT eventid, TimeStamp, TaskName, filepath FROM (SELECT eventid, TimeStamp, filepath, TaskName, ROW_NUMBER() OVER(PARTITION BY eventid ORDER BY TimeStamp DESC) AS seq FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') and (TimeStamp <= '2010-4-21 00:00:00.000'))) AS T WHERE seq = 1 order by eventid And the query below seems to accomplish #2: SELECT eventid, TimeStamp, TaskName, filepath FROM (SELECT eventid, TimeStamp, filepath, TaskName, ROW_NUMBER() OVER(PARTITION BY eventid ORDER BY TimeStamp DESC) AS seq FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T WHERE seq = 1 order by eventid This will bring back the eventid's that also have a TaskName='End': SELECT eventid, TimeStamp, TaskName, filepath FROM (SELECT eventid, TimeStamp, filepath, TaskName, ROW_NUMBER() OVER(PARTITION BY eventid ORDER BY TimeStamp DESC) AS seq FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T WHERE seq = 1 and eventid in (Select eventid from eventrecords where TaskName = 'End') order by eventid So I've tried the following to TRY to accomplish #3: SELECT eventid, TimeStamp, TaskName, filepath FROM (SELECT eventid, TimeStamp, filepath, TaskName, ROW_NUMBER() OVER(PARTITION BY eventid ORDER BY TimeStamp DESC) AS seq FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T WHERE seq = 1 and case when (eventid in (Select eventid from eventrecords where TaskName = 'End') then 1 else 0) as bit end order by eventid When I try to run this, I get: "Incorrect syntax near the keyword 'then'." Not sure what I'm doing wrong. Haven't seen any examples anywhere quite like this. I should mention that eventrecords has a primary key, but it doesn't seem to help anything when I include it, and I am not permitted to change the table. (ugh) I've received one suggestion to use a cursor and temporary table, but am not sure how badley that would bog down performance when the report is running. Thanks in advance.

  Read the article

• SQL Server - Get Inserted Record Identity Value when Using a View's Instead Of Trigger

  - by CuppM

  For several tables that have identity fields, we are implementing a Row Level Security scheme using Views and Instead Of triggers on those views. Here is a simplified example structure: -- Table CREATE TABLE tblItem ( ItemId int identity(1,1) primary key, Name varchar(20) ) go -- View CREATE VIEW vwItem AS SELECT * FROM tblItem -- RLS Filtering Condition go -- Instead Of Insert Trigger CREATE TRIGGER IO_vwItem_Insert ON vwItem INSTEAD OF INSERT AS BEGIN -- RLS Security Checks on inserted Table -- Insert Records Into Table INSERT INTO tblItem (Name) SELECT Name FROM inserted; END go If I want to insert a record and get its identity, before implementing the RLS Instead Of trigger, I used: DECLARE @ItemId int; INSERT INTO tblItem (Name) VALUES ('MyName'); SELECT @ItemId = SCOPE_IDENTITY(); With the trigger, SCOPE_IDENTITY() no longer works - it returns NULL. I've seen suggestions for using the OUTPUT clause to get the identity back, but I can't seem to get it to work the way I need it to. If I put the OUTPUT clause on the view insert, nothing is ever entered into it. -- Nothing is added to @ItemIds DECLARE @ItemIds TABLE (ItemId int); INSERT INTO vwItem (Name) OUTPUT INSERTED.ItemId INTO @ItemIds VALUES ('MyName'); If I put the OUTPUT clause in the trigger on the INSERT statement, the trigger returns the table (I can view it from SQL Management Studio). I can't seem to capture it in the calling code; either by using an OUTPUT clause on that call or using a SELECT * FROM (). -- Modified Instead Of Insert Trigger w/ Output CREATE TRIGGER IO_vwItem_Insert ON vwItem INSTEAD OF INSERT AS BEGIN -- RLS Security Checks on inserted Table -- Insert Records Into Table INSERT INTO tblItem (Name) OUTPUT INSERTED.ItemId SELECT Name FROM inserted; END go -- Calling Code INSERT INTO vwItem (Name) VALUES ('MyName'); The only thing I can think of is to use the IDENT_CURRENT() function. Since that doesn't operate in the current scope, there's an issue of concurrent users inserting at the same time and messing it up. If the entire operation is wrapped in a transaction, would that prevent the concurrency issue? BEGIN TRANSACTION DECLARE @ItemId int; INSERT INTO tblItem (Name) VALUES ('MyName'); SELECT @ItemId = IDENT_CURRENT('tblItem'); COMMIT TRANSACTION Does anyone have any suggestions on how to do this better? I know people out there who will read this and say "Triggers are EVIL, don't use them!" While I appreciate your convictions, please don't offer that "suggestion".

  Read the article

• grub2 loopback booting ubuntu server iso

  - by Thermionix

  I've got usb-keys setup to multi-boot different linux images using grub2 loopback. The standard ubuntu release isos boot fine, however when attempting to use the server iso it will fail to install saying 'unable to detect cd-media for installation'. the grub.cfg entry; menuentry "ubuntu-12.04-server-amd64" { set isofile="/boot/ubuntu-12.04-server-amd64.iso" loopback loop $isofile linux (loop)/install/vmlinuz file=$isofile/preseed/ubuntu-server.seed ro noprompt noeject -- initrd (loop)/install/initrd.gz } I've attempted to add the following parameters as the fromiso works for debian images on the key iso-scan/filename=/boot/ubuntu-12.04-server-amd64.iso iso-scan/filename=/dev/disk/by-uuid/2859-44B7/boot/ubuntu-12.04-server-amd64.iso fromiso=/dev/disk/by-uuid/2859-44B7/boot/ubuntu-12.04-server-amd64.iso

  Read the article

• Server 13.10 Install Hangs

  - by noloader

  I'm trying to install Server 13.10 on some old hardware for testing (HP/Compaq dc5850, with Athlon X2 2.3 GHz dual core, 4GB RAM). The third screen is "Select Language" and the machine appears to hang. Ath e selection screen, the keyboard and mouse does not work. I integrity checked the CD and it checked out OK. I also performed a second ISO download and it cross checked with my initial download: $ md5 Desktop/ubuntu-13.10-server-amd64.iso Downloads/ubuntu-13.10-server-amd64.iso MD5 (Desktop/ubuntu-13.10-server-amd64.iso) = 4d1a8b720cdd14b76ed9410c63a00d0e MD5 (Downloads/ubuntu-13.10-server-amd64.iso) = 4d1a8b720cdd14b76ed9410c63a00d0e I was able to install Server 12.04 on the same machine. The problem with 12.04 is the OpenStack packages are too old, and I wanted to try something with fresher packages. My problem appears similar to I cannot install Ubuntu Server 12.10. The Installer hangs at the language selection, but my ISOs appear to be good and the disc appears to be good. Any ideas?

  Read the article

• Help with complex sql query

  - by eugeneK

  To make story short, i'm building self-learning banner management system. Users will be able to insert these banners to their site when banners will be shown based on sales/impressions ratio. I have 4 tables Banners bannerID int bannerImage varchar.... SmartBanners smartBannerID int smartBannerArrayID int bannerID int impressionsCount int visibility tinyint (percents) SmartBannerArrays smartBannerArrayID int userID int Statistics bannerID int saleAmountPerDay decimal... Each night i need to generate new "visibility" for each SmartBanner based on whole SmartBannerArray that same user has. So i need to get sum of impressions and sales for each bannerID in SmartBannerArray. All comes to my mind is to use double cursor, one will loop thought SmartBannerArrays get needed values for sum of impressions and sales and then inner loop which will access each SmartBanner and change it's "visibility" percentage based on (sales/impressions)/(sumOfSales/sumOfImpressions)*100 Hope you get the picture... Is there any other way to design better tables or not to use double cursor to avoid server overload ?

  Read the article

• Migrating from SQL Server to firebird: pro and cons

  - by user193655

  I am considering the migration for 4 reasons: 1) SQLSERVER installation is a nightmare, expecially for 1-user software (Even if typically I have 3-20 users, sometimes I sell my software to single users: it is incredible to have troubles installing the DB, while installing the applicatino means copying an exe...). (note my max installation is 100 users, but there is no an upper limit). Software installs in 10 seconds, SQLServer in 1 hour. Firebird installation is much easier. 2) SQLSERVER runs on windows server only 3) My customers have all the express edition 4) i am not using any advanced feature, I am now starting using filestream, but the main reason for this is that Express edition has 4/10GB db size limit So these are all Pros of moving to Firebird. Which are the cons? I can also plan to support both platforms, but this will backfire I fear.

  Read the article

• regarding like query operator

  - by stackoverflowuser

  Hi For the below data (well..there are many more nodes in the team foundation server table which i need to refer to..below is just a sample) Nodes ------------------------ \node1\node2\node3\ \node1\node2\node5\ \node1\node2\node3\node4 I was wondering if i can apply something like (below query does not give the required results) select * from table_a where nodes like '\node1\node2\%\' to get the below data \node1\node2\node3\ \node1\node2\node5\ and something like (below does not give the required results) select * from table_a where nodes like '\node1\node2\%\%\' to get \node1\node2\node3\ \node1\node2\node5\ \node1\node2\node3\node4 Can the above be done with like operator? Pls. suggest. Thanks

  Read the article

• How do I use BCP or Sql Server Management Studio to get BLOB data our of Sql Server?

  - by Eric

  I'm sorry if this question has been asked already, but I couldn't find it anywhere. I have a table that stores files as BLOBS. The column that holds the file is an image datatype. I would like to be able to extract the binary data out of the column and turn it in to an actual file. I would ideally like to be able to do this with BCP or management studio if possible. I have tried BCP, but for some reason when I try and pull out an office document Word thinks it's corrupt. Here's what I've tried so far (obviously the values have been changed to protect the innocent :): bcp "select document_binary_data from database where id = 12345" queryout "c:\filename.doc" -n -S server -U username -P password This isn't working though? Any thoughts?

  Read the article

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

  - by Lloyd Banks

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

  Read the article

• Intermittent Connection Issues to SQL Server 2008 R2 RTM

  - by Chandan Jha

  The problem I am facing is a very complex one and inspite of trying to gather a root cause of the problem, I am standing at the same place after 2 months with just bits and pieces of information.Here is a scenario: There is a windows 2003 server which uses an system DSN ODBC connection. I looked into the driver properties and it is as follows: Name Version File SQL Server 2000.86.3959.00 SQLSRV32.DLL Now, this system DSN has been given configured with TCP\IP in Network Libraries and 'determine port dynamically' is checked. Now, lets come to the database destination. It is hosted on Windows 2008 having SQL 2008 R2 RTM version 64-bit. Now, I will give you a an overview about the events that happen and whatever troubleshooting I could perform: I get an email saying 'blah blah' failed and the only message their application gets is 'cannot connect to database' I go the SQL Server logs and find the following information: Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.0.0.xx Error: 18456, Severity: 14, State: 58.] A quick search shows that this error may come when an SQL Server is configured with windows authentication but its not true. We have mixed mode and connection issue is intermittent. This SQL Server is configured to run on a local system account but since we use only SQL Server accounts to connect to this, there should not be any Kerberos errors. When I run a profiler trace and see only 'existing connections', i see a lot of them coming from my client server displaying the sql user but NO hostname is shown. Textdata field shows TCP\IP information along with some arithabort and ansi-null settings. Now, I tried looking into ring connectivity buffer by using following: SELECTCAST(record AS XML) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY' One sample output is: <ConnectivityTraceRecord> <RecordType>Error</RecordType> <RecordSource>Tds</RecordSource> <Spid>118</Spid> <SniConnectionId>5124905D-D1EC-460E-AD78-201050B78C67</SniConnectionId> <OSError>0</OSError> <SniConsumerError>18452</SniConsumerError> <SniProvider>7</SniProvider> <State>1</State> <RemoteHost>10.0.0.21</RemoteHost> <RemotePort>5008</RemotePort> <LocalHost>10.1.0.38</LocalHost> <LocalPort>1433</LocalPort> <RecordTime>6/6/2012 21:14:57.527</RecordTime> <TdsBuffersInformation> <TdsInputBufferError>0</TdsInputBufferError> <TdsOutputBufferError>0</TdsOutputBufferError> <TdsInputBufferBytes>120</TdsInputBufferBytes> </TdsBuffersInformation> <TdsDisconnectFlags> <PhysicalConnectionIsKilled>0</PhysicalConnectionIsKilled> <DisconnectDueToReadError>0</DisconnectDueToReadError> <NetworkErrorFoundInInputStream>0</NetworkErrorFoundInInputStream> <ErrorFoundBeforeLogin>0</ErrorFoundBeforeLogin> <SessionIsKilled>0</SessionIsKilled> <NormalDisconnect>0</NormalDisconnect> </TdsDisconnectFlags> </ConnectivityTraceRecord> <Stack> <frame id="0">0X000000000174C34B</frame> <frame id="1">0X0000000001748FDD</frame> <frame id="2">0X0000000002461001</frame> <frame id="3">0X0000000000C47E98</frame> <frame id="4">0X00000000008015AD</frame> <frame id="5">0X0000000000801492</frame> <frame id="6">0X00000000003CBBD8</frame> <frame id="7">0X00000000003CB8BA</frame> <frame id="8">0X00000000003CB6FF</frame> <frame id="9">0X00000000008E8FB6</frame> <frame id="10">0X00000000008E9175</frame> <frame id="11">0X00000000008E9839</frame> <frame id="12">0X00000000008E9502</frame> <frame id="13">0X0000000074E437D7</frame> <frame id="14">0X0000000074E43894</frame> <frame id="15">0X00000000775A652D</frame> Somehow all the errors show error number 18452 whereas I never found this error in my SQL logs where I see only 18456. I am just stuck on a dead end because this connection issue appears intermittently. Sorry for a long question but I hope if you read this, you can make out that I tried a lot at my end before giving up.

  Read the article

• Is SQL Server DRI (ON DELETE CASCADE) slow?

  - by Aaronaught

  I've been analyzing a recurring "bug report" (perf issue) in one of our systems related to a particularly slow delete operation. Long story short: It seems that the CASCADE DELETE keys were largely responsible, and I'd like to know (a) if this makes sense, and (b) why it's the case. We have a schema of, let's say, widgets, those being at the root of a large graph of related tables and related-to-related tables and so on. To be perfectly clear, deleting from this table is actively discouraged; it is the "nuclear option" and users are under no illusions to the contrary. Nevertheless, it sometimes just has to be done. The schema looks something like this: Widgets | +--- Anvils (1:1) | | | +--- AnvilTestData (1:N) | +--- WidgetHistory (1:N) | +--- WidgetHistoryDetails (1:N) Nothing too scary, really. A Widget can be different types, an Anvil is a special type, so that relationship is 1:1 (or more accurately 1:0..1). Then there's a large amount of data - perhaps thousands of rows of AnvilTestData per Anvil collected over time, dealing with hardness, corrosion, exact weight, hammer compatibility, usability issues, and impact tests with cartoon heads. Then every Widget has a long, boring history of various types of transactions - production, inventory moves, sales, defect investigations, RMAs, repairs, customer complaints, etc. There might be 10-20k details for a single widget, or none at all, depending on its age. So, unsurprisingly, there's a CASCADE DELETE relationship at every level here. If a Widget needs to be deleted, it means something's gone terribly wrong and we need to erase any records of that widget ever existing, including its history, test data, etc. Again, nuclear option. Relations are all indexed, statistics are up to date. Normal queries are fast. The system tends to hum along pretty smoothly for everything except deletes. Getting to the point here, finally, for various reasons we only allow deleting one widget at a time, so a delete statement would look like this: DELETE FROM Widgets WHERE WidgetID = @WidgetID Pretty simple, innocuous looking delete... that takes over 2 minutes to run, for a widget with no data! After slogging through execution plans I was finally able to pick out the AnvilTestData and WidgetHistoryDetails deletes as the sub-operations with the highest cost. So I experimented with turning off the CASCADE (but keeping the actual FK, just setting it to NO ACTION) and rewriting the script as something very much like the following: DECLARE @AnvilID int SELECT @AnvilID = AnvilID FROM Anvils WHERE WidgetID = @WidgetID DELETE FROM AnvilTestData WHERE AnvilID = @AnvilID DELETE FROM WidgetHistory WHERE HistoryID IN ( SELECT HistoryID FROM WidgetHistory WHERE WidgetID = @WidgetID) DELETE FROM Widgets WHERE WidgetID = @WidgetID Both of these "optimizations" resulted in significant speedups, each one shaving nearly a full minute off the execution time, so that the original 2-minute deletion now takes about 5-10 seconds - at least for new widgets, without much history or test data. Just to be absolutely clear, there is still a CASCADE from WidgetHistory to WidgetHistoryDetails, where the fanout is highest, I only removed the one originating from Widgets. Further "flattening" of the cascade relationships resulted in progressively less dramatic but still noticeable speedups, to the point where deleting a new widget was almost instantaneous once all of the cascade deletes to larger tables were removed and replaced with explicit deletes. I'm using DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test. I've disabled all triggers that might be causing further slowdowns (although those would show up in the execution plan anyway). And I'm testing against older widgets, too, and noticing a significant speedup there as well; deletes that used to take 5 minutes now take 20-40 seconds. Now I'm an ardent supporter of the "SELECT ain't broken" philosophy, but there just doesn't seem to be any logical explanation for this behaviour other than crushing, mind-boggling inefficiency of the CASCADE DELETE relationships. So, my questions are: Is this a known issue with DRI in SQL Server? (I couldn't seem to find any references to this sort of thing on Google or here in SO; I suspect the answer is no.) If not, is there another explanation for the behaviour I'm seeing? If it is a known issue, why is it an issue, and are there better workarounds I could be using?

  Read the article

• Get mutually and non mutually existening Fields in same table in Two columns

  - by ranabra

  This is a question similar to another question I posted here but is a little different. I am trying to get a list of all instances of mutual and non-mutual existing Users. What I mean is that the returned result from the query will return a list of users along with their co-worker. It is similar to the question here, but the difference is that non mutual users will be returned too and with out the "duplicity" mutually existing users return in the list (See image below in-order simplify it all). I took the original answer from Thomas (Thanx again Thomas) Select D1.u_username, U1.Permission, U1.Grade, D1.f_username, U2.Permission, U2.Gradefrom tblDynamicUserList As D1    Join tblDynamicUserList As D2        On D2.u_username = D1.f_username            And D2.f_username = D1.u_username    Join tblUsers As U1        On U1.u_username = D1.u_username    Join tblUsers As U2        On U2.u_username = D2.u_username and after some several trials I commented out 2 lines (Below). The returned result are exactly as described in the beginning of this question, but with the "duplicity" returned by mutually existing users in the table. How can I eliminate this duplicity? Select D1.u_username, U1.Permission, U1.Grade, D1.f_username, U2.Permission, U2.Gradefrom tblDynamicUserList As D1    Join tblDynamicUserList As D2        On D2.u_username = D1.f_username            /* And D2.f_username = D1.u_username */    Join tblUsers As U1        On U1.u_username = D1.u_username    Join tblUsers As U2        On U2.u_username = D2.u_username /* WHERE D1.U_userName < D1.f_username */ *Screenshot that hopefully helps explain it all. Database is SQL 2005. Many thanx in advance

  Read the article

• Is there a way to delay compilation of a stored procedure's execution plan?

  - by Ian Henry

  (At first glance this may look like a duplicate of http://stackoverflow.com/questions/421275 or http://stackoverflow.com/questions/414336, but my actual question is a bit different) Alright, this one's had me stumped for a few hours. My example here is ridiculously abstracted, so I doubt it will be possible to recreate locally, but it provides context for my question (Also, I'm running SQL Server 2005). I have a stored procedure with basically two steps, constructing a temp table, populating it with very few rows, and then querying a very large table joining against that temp table. It has multiple parameters, but the most relevant is a datetime "@MinDate." Essentially: create table #smallTable (ID int) insert into #smallTable select (a very small number of rows from some other table) select * from aGiantTable inner join #smallTable on #smallTable.ID = aGiantTable.ID inner join anotherTable on anotherTable.GiantID = aGiantTable.ID where aGiantTable.SomeDateField > @MinDate If I just execute this as a normal query, by declaring @MinDate as a local variable and running that, it produces an optimal execution plan that executes very quickly (first joins on #smallTable and then only considers a very small subset of rows from aGiantTable while doing other operations). It seems to realize that #smallTable is tiny, so it would be efficient to start with it. This is good. However, if I make that a stored procedure with @MinDate as a parameter, it produces a completely inefficient execution plan. (I am recompiling it each time, so it's not a bad cached plan...at least, I sure hope it's not) But here's where it gets weird. If I change the proc to the following: declare @LocalMinDate datetime set @LocalMinDate = @MinDate --where @MinDate is still a parameter create table #smallTable (ID int) insert into #smallTable select (a very small number of rows from some other table) select * from aGiantTable inner join #smallTable on #smallTable.ID = aGiantTable.ID inner join anotherTable on anotherTable.GiantID = aGiantTable.ID where aGiantTable.SomeDateField > @LocalMinDate Then it gives me the efficient plan! So my theory is this: when executing as a plain query (not as a stored procedure), it waits to construct the execution plan for the expensive query until the last minute, so the query optimizer knows that #smallTable is small and uses that information to give the efficient plan. But when executing as a stored procedure, it creates the entire execution plan at once, thus it can't use this bit of information to optimize the plan. But why does using the locally declared variables change this? Why does that delay the creation of the execution plan? Is that actually what's happening? If so, is there a way to force delayed compilation (if that indeed is what's going on here) even when not using local variables in this way? More generally, does anyone have sources on when the execution plan is created for each step of a stored procedure? Googling hasn't provided any helpful information, but I don't think I'm looking for the right thing. Or is my theory just completely unfounded? Edit: Since posting, I've learned of parameter sniffing, and I assume this is what's causing the execution plan to compile prematurely (unless stored procedures indeed compile all at once), so my question remains -- can you force the delay? Or disable the sniffing entirely? The question is academic, since I can force a more efficient plan by replacing the select * from aGiantTable with select * from (select * from aGiantTable where ID in (select ID from #smallTable)) as aGiantTable Or just sucking it up and masking the parameters, but still, this inconsistency has me pretty curious.

  Read the article

< Previous Page | 147 148 149 150 151 152 153 154 155 156 157 158  | Next Page >