Search Results

Search found 62701 results on 2509 pages for 'sql function'.

Page 49/2509 | < Previous Page | 45 46 47 48 49 50 51 52 53 54 55 56  | Next Page >

• Store time of the day in SQL

  - by nute

  How would you store a time or time range in SQL? It won't be a datetime because it will just be let's say 4:30PM (not, January 3rd, 4:30pm). Those would be weekly, or daily meetings. The type of queries that I need are of course be for display, but also later will include complex queries such as avoiding conflicts in schedule. I'd rather pick the best datatype for that now. I'm using MS SQL Server Express 2005. Thanks! Nathan

  Read the article

• Convert SQL to LINQ to SQL

  - by Adam

  Hi I have the SQL query with c as ( select categoryId,parentId, name,0 as [level] from task_Category b where b.parentId is null union all select b.categoryId,b.parentId,b.name,[level] + 1 from task_Category b join c on b.parentId = c.categoryId) select name,[level],categoryId,parentId as item from c and I want to convert it to LINQ to SQL, yet my LINQ skills are not there yet. Could someone please help me convert this. It's the with and union statements that are making this a bit more complex for me. Any help appreciated.

  Read the article

• ROW_NUMBER Alt in SQL Server 2000

  - by harekam_taj

  Hey Guys, Can anyone please help me with an alt. to ROW_NUMBER in SQL Server 2000. RIGHT now my procedure looks like this in SQL Server 2008 WITH cars as(SELECT carid,mileage,retailprice,imageurl,model,year, Zips.Distance AS Miles, Manufacturers.mfgName as Make,dealers.companyname as companyname, CASE @sortby WHEN 'D' THEN ROW_NUMBER() OVER (ORDER BY Manufacturers.mfgName) WHEN 'P' THEN ROW_NUMBER() OVER (ORDER BY retailprice) WHEN 'M' THEN ROW_NUMBER() OVER (ORDER BY mileage) END as 'rownum' FROM usedcars INNER JOIN #TempZips Zips ON Zips.ZipCode =usedcars.loczip left join Manufacturers on Manufacturers.mfgid=usedcars.mfgid left join dealers on dealers.dealerid = usedcars.dealerid where usedcars.active=1 and usedcars.dealerid=@dealerid) select @totalrecords as totalrec,* from cars where rownum between @skip and @take

  Read the article

• SELECT with a Replace()

  - by andyjohnson

  I have a table of names and addresses, which includes a postcode column. I want to strip the spaces from the postcodes and select any that match a particular pattern. I'm trying this (simplified a bit) in T-SQL on SQL Server 2005: SELECT Replace(Postcode, ' ', '') AS P FROM Contacts WHERE P LIKE 'NW101%' But I get the following error; Msg 207, Level 16, State 1, Line 3 Invalid column name 'P'. If I remove the WHERE clause I get a list of postcodes without spaces, which is what I want to search. How should I approach this? What am I doing wrong?

  Read the article

• Very Different Execution Times of SQL Query in C# and SQL Server Management Studio

  - by Paul

  I have a simple SQL query that when run from C# takes over 30 seconds then times-out every time, whereas when run on SQL Server Management Studio successfully completes instantly. In the latter case, a query execution plan reveals nothing troubling, and the execution time is spread nicely through a few simple operations. I've run 'EXEC sp_who2' while the query is running from C#, and it is listed as taking 29,000 milliseconds of CPU time, and is not blocked by anything. I have no idea how to begin solving this. Does anyone have some insight? The query is: SELECT c.lngId, ... FROM tblCase c INNER JOIN tblCaseStatus s ON s.lngId = c.lngId INNER JOIN tblCaseStatusType t ON t.lngId = s.lngId INNER JOIN [Another Database]..tblCompany cm ON cm.lngId = cs.lngCompanyId WHERE t.lngId = 25 AND c.IsDeleted = 0 AND s.lngStatus = 1

  Read the article

• SQL Script To Generate a Database Dictionary **With Linked Fields**

  - by Albert

  I would like to generate a Data Dictionary for a SQL Server 2008 database that has one row for each field, and the following columns: table_name field_name data_type link_table (for when the field in question is a foreign key) link_field (for when the field in question is a foreign key) I can get the first 3 columns with something like the SQL script below...but I don't know how to get the last two columns of foreign key information. INFORMATION_SCHEMA.TABLE_CONSTRAINTS gets close, but doesn't have the data I'm looking for. Can someone help with this point? SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS Secondarily if anyone has any suggestions on additional fields which would be helpful please post.

  Read the article

• alias not working on sql server 2008 r2

  - by Saba

  I have several servers with SQL Server 2008 R2 instances on them, and alias doesn't work on any of them. Clients connect to these servers using TCP/IP without any problem, telnet works on IP/Port I use for my alias, the firewall exceptions are created, basically everything works fine, except when I create an alias, I can not connect through it to my server using either TCP/IP or named pipes (local or one of other servers). I've installed latest cumulative updates, which updates native client too (which I think is the source of problem) and I still have the problem. The stranger part is, if I create an alias on a server with sql server 2005 (native client 9), I can connect to my 2008 r2 instances. Any suggestions?

  Read the article

• Cumulative Update packages for SQL Server 2008 are available now: CU7 for SQL2008 SP2 and CU2 for SQL2008 SP3

  - by ssqa.net

  Another instalment of Cumulative Update package for SQL Server 2008 SP3 is available now, which is CU2 and the build number is known as 10.00.5768.00. As usual this CU2 for SQL2008 SP3 contains hotfixes for issues that were fixed after the release of SQL Server 2008 Service Pack 3 (SP3). KBA2633143 list the following article numbers about more information on the fixes: VSTS bug number KB article number Description 794387 2522893 (http://support.microsoft.com/kb/2522893/ ) FIX: A backup operation...(read more)

  Read the article

• Microsoft SQL Server 2008 R2 Administration Cookbook - Book and eBook expected June 2011. Pre-order now!

  - by ssqa.net

  Over 85 practical recipes for administering a high-performance SQL Server 2008 R2 system. Book and eBook expected June 2011 . Pre-order now! Multi-format orders get free access on PacktLib , This practical cookbook will show you the advanced administration techniques for managing and administering a scalable and high-performance SQL Server 2008 R2 system. It contains over 85 practical, task-based, and immediately useable recipes covering a wide range of advanced administration techniques for administering...(read more)

  Read the article

• SQL Developer Blitz at ODTUG Kscope12

  - by thatjeffsmith

  Oracle Development Tools User Group (ODTUG) puts on an outstanding event, and I enjoy that the content comes FIRST. Yes, the after-event parties and entertainment are first class, but I look forward most to sitting in on some excellent sessions. For Kscope12 one would expect Oracle to have a large presence, and you would be absolutely correct! The APEX team will be there in full force, and we’ll have sessions on JDeveloper, ADF, and .NET. But what I want to talk about today is our awesome line-up of coverage for Oracle SQL Developer (Surprise!) DB and Developer’s Toolbox Symposium Kris Rice or @krisrice, Product Development Manager for SQL Developer, will speak at 10AM Sunday about SQL Developer Data Modeler. Our free data modeling solution allows one to reverse engineer a data dictionary to a model, modify it, and create a script of the changes. Collaboration is an important part of any development team; with built-in subversion support, the modeler makes collaboration easy, not just possible. After the morning break, I’ll be talking about SQL Developer’s PL/SQL support. From creating your code, to debugging, tuning, testing, and documenting PL/SQL – SQL Developer fits the bill. Since I have a full hour, I should have time to do a little riff on using source control to version and manage your revisions too! At 3:15 Jagan Athreya will talk about the new integration between SQL Developer and Enterprise Manager Cloud Control 12c. Enabling developers to define changes in SQLDeveloper and allowing DBAs to promote these changes to Test and Production via Enterprise Manager will reduce errors, accelerate productivity, and help eliminate unplanned downtime. Get your SQL Developer groove on at ODTUG Kscope12! Presentations SQL Developer Tips and Tricks Monday June 25, Session 5, 4:15 pm – 5:15 pm I’ll take you through my favorite keyboard shortcuts, top 10 preferences every user should tweak, and spotlight features that the average user probably hasn’t discovered yet. My goal for this session is for everyone to take 1-2 tips they can implement immediately to save mucho time. I enjoy interacting with the audience so no two versions of this presentation are the same. Oracle SQL Developer and Data Modeler New Features When: Tuesday June 26, Session 6, 8:30 am – 9:30 am Ashley Chen, my PM-partner-in-crime, will be covering all the new features from our two latest updates. So if you’re new to SQL Developer, or you’ve been using an older version, stop by and see what new toys you have to play with. I also have a bet with Ashley that she will have more attendees than me, so be sure to show up so I can collect. Debugging PL/SQL With SQL Developer When: Wednesday June 27, Session 16, 3:00 pm – 4:00 pm Me again – sorry. This time I have an entire hour to JUST talk about PL/SQL and debugging! Should you use a watch with a break condition, or a breakpoint with a passcount? How does external debugging with a Perl script work? Can I just debug an anonymous PL/SQL block. So if debugging to you is just a DBMS_OUTPUT.PUT_LINE() call, stop by and see how our IDE can help you take things to the next level! Or is that level++? Hands-on-Training SQL Developer Soup to Nuts When: Tuesday, 8:00 AM – 9:30 AM If you learn by doing, this is the session for you. Bring your own laptop or use one of the lab machines. We’ll give you a VirtualBox OEL image running 11gR2 EE Database with all the fixin’s (that’s Southern speak for Partitioning, Advanced Compression, Tuning & Diagnostic Packs, etc), TimesTen, APEX and much more. All you have to do is login and run through our lab exercises. You can start with a model and work your way up to debugging and testing your own appliction, or you can pick and choose your lessons to suit your needs. We’ll have people on hand to help you out and answer your questions. Booth Hours We’ll be in the vendor area and have our very own ‘demo pod’ for SQL Developer. Between Kris, Ashley, and I we should be able to answer your questions or show you how to ‘do that thing’ in the tool. Or just stop by and say hello! We’ll be around the following hours’ish: Sunday, June 24, 2012 6:00 PM – 8:00 PM Monday, June 25, 2012 9:00 AM – 4:30 PM Tuesday, June 26, 2012 9:30 AM – 3:30 PM Wednesday, June 27, 2012 10:15 AM – 2:00 PM No Excuses – If You Have Questions, This is Your Chance to Get Your Answers! We’re doing just about everything outside of a scavenger hunt to bring information and value to our users. Let us know what you like, what you don’t like, and we’ll do our best to do more of the former and less of the latter!

  Read the article

• SQL*Plus??? - SQL??????????????????(????? ???Tips-3)

  - by Yuichi.Hayashi

  ??SQL??????????????? $ sqlplus @batch1.sql ?????SQL????????????????????????????????????? ????????????????????????????SQL?????????????????????????? SQL*Plus?????????????????????????????????????????????????SQL????????????????? ???????????????????????? #!/bin/sh sqlplus -s /nolog    conn scott/tiger"    select sysdate from dual;    exit EOF ??????????????????SQL???????????? ?EOF?????????EndOfSQL??????????????????????????????????????????????????? ????????????????????????????????? ????????????????????????SQL???????????????????????????SQL????????????????????????????????????????? ??????SELECT???????????????????????? #!/bin/sh table_name=dual sqlplus -s /nolog    conn scott/tiger    select sysdate from $table_name;    exit EndOfSQL (Written by Hiroyuki Nakaie)

  Read the article

• Different function returns from command line and within function

  - by Myx

  Hello: I have an extremely bizzare situation: I have a function in MATLAB which calls three other main functions and produces two figures for me. The function reads in an input jpeg image, crops it, segments it using kmeans clustering, and outputs 2 figures to the screen - the original image and the clustered image with the cluster centers indicated. Here is the function in MATLAB: function [textured_avg_x photo_avg_x] = process_database_images() clear all warning off %#ok type_num_max = 3; % type is 1='texture', 2='graph', or 3='photo' type_num_max = 1; img_max_num_photo = 100; % 400 photo images img_max_num_other = 100; % 100 textured, and graph images for type_num = 1:2:type_num_max if(type_num == 3) img_num_max = img_max_num_photo; else img_num_max = img_max_num_other; end img_num_max = 1; for img_num = 1:img_num_max [type img] = load_image(type_num, img_num); %img = imread('..\images\445.jpg'); img = crop_image(img); [IDX k block_bounds features] = segment_image(img); end end end The function segment_image first shows me the color image that was passed in, performs kmeans clustering, and outputs the clustered image. When I run this function on a particular image, I get 3 clusters (which is not what I expect to get). When I run the following commands from the MATLAB command prompt: >> img = imread('..\images\texture\1.jpg'); >> img = crop_image(img); >> segment_image(img); then the first image that is displayed by segment_image is the same as when I run the function (so I know that the clustering is done on the same image) but the number of clusters is 16 (which is what I expect). In fact, when I run my process_database_images() function on my entire image database, EVERY image is evaluated to have 3 clusters (this is a problem), whereas when I test some images individually, I get in the range of 12-16 clusters, which is what I prefer and expect. Why is there such a discrepancy? Am I having some syntax bug in my process_database_images() function? If more code is required from me (i.e. segment_images function, or crop_image function), please let me know. Thanks.

  Read the article

• MATLAB: different function returns from command line and within function

  - by Myx

  Hello: I have an extremely bizzare situation: I have a function in MATLAB which calls three other main functions and produces two figures for me. The function reads in an input jpeg image, crops it, segments it using kmeans clustering, and outputs 2 figures to the screen - the original image and the clustered image with the cluster centers indicated. Here is the function in MATLAB: function [textured_avg_x photo_avg_x] = process_database_images() clear all warning off %#ok type_num_max = 3; % type is 1='texture', 2='graph', or 3='photo' type_num_max = 1; img_max_num_photo = 100; % 400 photo images img_max_num_other = 100; % 100 textured, and graph images for type_num = 1:2:type_num_max if(type_num == 3) img_num_max = img_max_num_photo; else img_num_max = img_max_num_other; end img_num_max = 1; for img_num = 1:img_num_max [type img] = load_image(type_num, img_num); %img = imread('..\images\445.jpg'); img = crop_image(img); [IDX k block_bounds features] = segment_image(img); end end end The function segment_image first shows me the color image that was passed in, performs kmeans clustering, and outputs the clustered image. When I run this function on a particular image, I get 3 clusters (which is not what I expect to get). When I run the following commands from the MATLAB command prompt: >> img = imread('..\images\texture\1.jpg'); >> img = crop_image(img); >> segment_image(img); then the first image that is displayed by segment_image is the same as when I run the function (so I know that the clustering is done on the same image) but the number of clusters is 16 (which is what I expect). In fact, when I run my process_database_images() function on my entire image database, EVERY image is evaluated to have 3 clusters (this is a problem), whereas when I test some images individually, I get in the range of 12-16 clusters, which is what I prefer and expect. Why is there such a discrepancy? Am I having some syntax bug in my process_database_images() function? If more code is required from me (i.e. segment_images function, or crop_image function), please let me know. Thanks.

  Read the article

• Can't remotely connect through SQL Server Management Studio

  - by FAtBalloon

  I have setup a SQL Server 2008 Express instance on a dedicated Windows 2008 Server hosted by 1and1.com. I cannot connect remotely to the server through management studio. I have taken the following steps below and am beyond any further ideas. I have researched the site and cannot figure anything else out so please forgive me if I missed something obvious, but I'm going crazy. Here's the lowdown. The SQL Server instance is running and works perfectly when working locally. In SQL Server Management Studio, I have checked the box "Allow Remote Connections to this Server" I have removed any external hardware firewall settings from the 1and1 admin panel Windows firewall on the server has been disabled, but just for kicks I added an inbound rule that allows for all connections on port 1433. In SQL Native Client configuration, TCP/IP is enabled. I also made sure the "IP1" with the server's IP address had a 0 for dynamic port, but I deleted it and added 1433 in the regular TCP Port field. I also set the "IPALL" TCP Port to 1433. In SQL Native Client configuration, SQL Server Browser is also running and I also tried adding an ALIAS in the I restarted SQL server after I set this value. Doing a "netstat -ano" on the server machine returns a TCP 0.0.0.0:1433 LISTENING UDP 0.0.0.0:1434 LISTENING I do a port scan from my local computer and it says that the port is FILTERED instead of LISTENING. I also tried to connect from Management studio on my local machine and it is throwing a connection error. Tried the following server names with SQL Server and Windows Authentication marked in the database security. ipaddress\SQLEXPRESS,1433 ipaddress\SQLEXPRESS ipaddress ipaddress,1433 tcp:ipaddress\SQLEXPRESS tcp:ipaddress\SQLEXPRESS,1433

  Read the article

• Can't connect to Sql Server 2008 named instance

  - by eidylon

  I just installed Sql 2008 Express on a new server running Windows Server 2008. I know Sql is working properly, because I can connect to the db fine locally, on the server. I cannot connect to it from a client machine though, neither by IP address nor by machine name (iporname\instance). I know I have the correct IP address, because I am RDCing into the server to perform all this configuration and setup, and if I ping the server name, it is resolving to the correct IP address as well. On the server, I have set up an inbound firewall exception allowing all traffic on any port on any protocol to sqlservr.exe. In SSMS, in server > Properties > Connections Allow remote connections to this server is enabled. In Sql Server Configuration Manager, TCP/IP is enabled in both the Protocols for <instance> and the Client Protocols sections. I looked in the Windows logs, but don't see anything about connections being denied or dropped. As far as I can see, I have everything set right, but cannot connect from a client machine. The client CAN connect to other Sql 2008 Express servers okay, so I know the client configuration is correct. Any ideas where else I can look for info of what/where/how this connection is dropping, greatly apprecaited! The error being returned by the client is: **TITLE: Connect to Server** Cannot connect to [MY.IP.ADD.RSS]\[MYINSTNAME]. ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

  Read the article

• attach / detach mssql 2008 sql server manager [SOLVED]

  - by Tillebeck

  An external consult wrote a guide on how to copy a database. Step two was detach the database using Sql Server Manager. After the detach the database was not visible in the SQL Server Manager... Not much to do but write a mail to the service provider asking to have the database attached again. The service porviders answer: Not posisble to attach again since the SQL Server security has been violated". Rolling back to last backup is not the option I want to use. Can any one give feedback if this seems logic and reasonable to assume that a detached database in a SQL Server 2008 accessed through SQL Server Manager cannot be reattached. It was done by rightclicking the database and choosing detach. -- update -- Based on the comments below I update the question with the server setup. There are two dedicated servers: srv1: Web server with remote desktop and an Sql Server Manager srv2: Sql server that can be accessed through the Sql Server Manager on the web server -- update2 -- After a restart of the server the DBA could suddenly do the attachment of the database. And I guess that after the restart it was a simple task. So all of your answer were rigth! It seems that I can only mark one as a correct answer so I marked the first answer correct. But all are correct answer. Thanks a lot. Without posting the link to this thread then we might had so suffer while watching our database beeing restored by a backup :-) Thanks a lot. BR. Anders

  Read the article

• Memory Usage of SQL Server

  - by Ashish

  SQL Server instance on my server is using almost full memory available in my Physical Server. Say if i am having 8GB of RAM than SQL Server is using 7.8 GB of RAM from system. I also have read articles and also read many similar questions regarding same on this forum and i understand that memory is reserved and it is using memory. But i have 2 same servers and 2 SQL Servers, why this is happening on a single SQL Instance not on other. Also when i run DBCC MemoryStatus than it is showing up... VM Reserved 8282008 VM Committed 537936 so from this we know that SQL reserved whole 8GB memory, but why this VM Committed keeps increasing. What i understand is VM Committed is: VM Committed: This value shows the overall amount of VAS that SQL Server has committed. VAS that is committed has been associated with physical memory. So this is the memory SQL Server has committed (from this i understand that physical memory actually SQL Server is using at instance). So like to know the reason behind this ever increasing VM Committed memory on my server and not on another. Thanks in Advance.

  Read the article

• How do i play nicely with MS SQL/SQL Server 2008

  - by acidzombie24

  Big problem. I have nearly given up. I am trying to port my prototype to use MS SQL so it will work on a server once i get it (the server will be SQL Server 2008, shared, i dont know any more info). So i tried to connect to SQL Server via visual studios IDE and had no luck. I enabled TCP and named pipes and restarted the service (and computer) with still no luck. I remembered about mdf files so i made that after an obstacle of not being able to make the connect string require i figure out visual studio has it in its properties and successfully connected with that. Then i had a problem with nested transactions. After not being able to figure out how to check i wondered if i can configure it to allow it somehow. I always thought all of MS were the same except for limitations but sql server seems to support nested transactions so theres no point trying to work around the problem with .mdf files since i wont need them and really just used it to port the base of my sql code and to check if syntax is correct. I tried installing SQL Server Management Studio since people mentioned it several times (as a solution or at least help). When installing it on windows 7 it says it may not be compatible. After running it, it launched SQL Server Installation Center (64-bit) which doesnt seem to be the same thing as i dont see a way to modify any of my server (networking) configurations or edit user permissions, etc. I am clueless what to do next. Does anyone have any ideas? I'm posting here bc i think my problem is more configurations and sql server then programming.

  Read the article

• SQL Server PowerShell Provider follows the Version of PowerShell on the Host and other errata

  - by BuckWoody

  There may be some misunderstanding on how the PowerShell Provider for SQL Server works. I’ve written an article or two explaining that you can use PowerShell with SQL Server, without having the SQL Server 2008 (or higher) provider around. After all, PowerShell just uses .NET, and SQL Server “Server Management Objects” or SMO listen to that interface as well. In SQL Server 2008 and higher we created a “MiniShell” for PowerShell that gives you the ability to treat a SQL Server Instance as a drive (called a “Provider” or path or drive) and a few commands (called command-lets). Using these two simple constructs you can move around SQL Server quickly and work with the objects it holds. I read the other day where someone stated that we had “re-compiled” PowerShell, so that you would have version 1.0 from SQL Server and 2.0 on your new server. Not so! Drop to a SQLPS prompt and a PowerShell prompt and type this in each: $PSVersionTable They should return the same value. You can think of a MiniShell as simply a compiled “profile” that gives you those providers and command-lets automatically – that’s all. In fact, you can load the SMO libraries yourself without the SQL Server 2008 Provider anywhere in sight. I do this all the time, since the MiniShell also has other restrictions. Also remember that if you run a PowerShell script as a SQL Agent Job step type (in 2008 and higher) that you’re running under the context of the account that starts Agent – I think most folks know this, but it’s good to keep in mind. There’s a re-written section of Books Online that goes over working with this very nicely – also covers the question “How to I connect to another server using the SQL Server PowerShell Provider” (hint: It’s just CD) and “How do I load all the SMO stuff if I don’t want to use the Provider” and more. Be sure and check out the note at the bottom that explains the firewall exceptions you’ll need to enable to CD to that remote server. Here’s that link: http://msdn.microsoft.com/en-us/library/cc281947.aspx Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• Microsoft guarantees the performance of SQL Server

  - by simonsabin

  I have recently been informed that Microsoft will be guaranteeing the performance of SQL Server. Yes thats right Microsoft will guarantee that you will get better performance out of SQL Server that any other competitor system. However on the flip side there are also saying that end users also have to guarantee the performance of SQL Server if they want to use the next release of SQL Server targeted for 2011 or 2012. It appears that a recent recruit Mark Smith from Newcastle, England will be heading a new team that will be making sure you are running SQL Server on adequate hardware and making sure you are developing your applications according to best practices. The Performance Enforcement Team (SQLPET) will be a global group headed by mark that will oversee two other groups the existing Customer Advisory Team (SQLCAT) and another new team the Design and Operation Group (SQLDOG). Mark informed me that the team was originally thought out during Yukon and was going to be an independent body that went round to customers making sure they didn’t suffer performance problems. However it was felt that they needed to wait a few releases until SQL Server was really there. The original Yukon Independent Performance Enhancement Team (YIPET) has now become the SQL Performance Enforcement Team (SQLPET). When challenged about the change from enhancement to enforcement Mark was unwilling to comment. An anonymous source suggested that "..Microsoft is sick of the bad press SQL Server gets for performance when the performance problems are normally down to people developing applications badly and using inadequate hardware..." Its true that it is very easy to install and run SQL, unlike other RDMS systems and the flip side is that its also easy to get into performance problems due to under specified hardware and bad design. Its not yet confirmed if this enforcement will apply to all SKUs or just the high end ones. I would personally welcome some level of architectural and hardware advice service that clients would be able to turn to, in order to justify getting the appropriate hardware at the start of a project and not 1 year in when its often too late.

  Read the article

• SQL Server and Hyper-V Dynamic Memory Part 2

  - by SQLOS Team

  Part 1 of this series was an introduction and overview of Hyper-V Dynamic Memory. This part looks at SQL Server memory management and how the SQL engine responds to changing OS memory conditions.   Part 2: SQL Server Memory Management As with any Windows process, sqlserver.exe has a virtual address space (VAS) of 4GB on 32-bit and 8TB in 64-bit editions. Pages in its VAS are mapped to pages in physical memory when the memory is committed and referenced for the first time. The collection of VAS pages that have been recently referenced is known as the Working Set. How and when SQL Server allocates virtual memory and grows its working set depends on the memory model it uses. SQL Server supports three basic memory models:   1. Conventional Memory Model   The Conventional model is the default SQL Server memory model and has the following properties: - Dynamic - can grow or shrink its working set in response to load and external (operating system) memory conditions. - OS uses 4K pages – (not to be confused with SQL Server “pages” which are 8K regions of committed memory).- Pageable - Can be paged out to disk by the operating system.   2. Locked Page Model The locked page memory model is set when SQL Server is started with "Lock Pages in Memory" privilege*. It has the following characteristics: - Dynamic - can grow or shrink its working set in the same way as the Conventional model.- OS uses 4K pages - Non-Pageable – When memory is committed it is locked in memory, meaning that it will remain backed by physical memory and will not be paged out by the operating system. A common misconception is to interpret "locked" as non-dynamic. A SQL Server instance using the locked page memory model will grow and shrink (allocate memory and release memory) in response to changing workload and OS memory conditions in the same way as it does with the conventional model.   This is an important consideration when we look at Hyper-V Dynamic Memory – “locked” memory works perfectly well with “dynamic” memory.   * Note in “Denali” (Standard Edition and above), and in SQL 2008 R2 64-bit (Enterprise and above editions) the Lock Pages in Memory privilege is all that is required to set this model. In 2008 R2 64-Bit standard edition it also requires trace flag 845 to be set, in 2008 R2 32-bit editions it requires sp_configure 'awe enabled' 1.   3. Large Page Model The Large page model is set using trace flag 834 and potentially offers a small performance boost for systems that are configured with large pages. It is characterized by: - Static - memory is allocated at startup and does not change. - OS uses large (>2MB) pages - Non-Pageable The large page model is supported with Hyper-V Dynamic Memory (and Hyper-V also supports large pages), but you get no benefit from using Dynamic Memory with this model since SQL Server memory does not grow or shrink. The rest of this article will focus on the locked and conventional SQL Server memory models.   When does SQL Server grow? For “dynamic” configurations (Conventional and Locked memory models), the sqlservr.exe process grows – allocates and commits memory from the OS – in response to a workload. As much memory is allocated as is required to optimally run the query and buffer data for future queries, subject to limitations imposed by:   - SQL Server max server memory setting. If this configuration option is set, the buffer pool is not allowed to grow to more than this value. In SQL Server 2008 this value represents single page allocations, and in “Denali” it represents any size page allocations and also managed CLR procedure allocations.   - Memory signals from OS. The operating system sets a signal on memory resource notification objects to indicate whether it has memory available or whether it is low on available memory. If there is only 32MB free for every 4GB of memory a low memory signal is set, which continues until 64MB/4GB is free. If there is 96MB/4GB free the operating system sets a high memory signal. SQL Server only allocates memory when the high memory signal is set.   To summarize, for SQL Server to grow you need three conditions: a workload, max server memory setting higher than the current allocation, high memory signals from the OS.    When does SQL Server shrink caches? SQL Server as a rule does not like to return memory to the OS, but it will shrink its caches in response to memory pressure. Memory pressure can be divided into “internal” and “external”.   - External memory pressure occurs when the operating system is running low on memory and low memory signals are set. The SQL Server Resource Monitor checks for low memory signals approximately every 5 seconds and it will attempt to free memory until the signals stop.   To free memory SQL Server does the following: ·         Frees unused memory. ·         Notifies Memory Manager Clients to release memory o   Caches – Free unreferenced cache objects. o   Buffer pool - Based on oldest access times.   The freed memory is released back to the operating system. This process continues until the low memory resource notifications stop.    - Internal memory pressure occurs when the size of different caches and allocations increase but the SQL Server process needs to keep its total memory within a target value. For example if max server memory is set and certain caches are growing large, it will cause SQL to free memory for re-use internally, but not to release memory back to the OS. If you lower the value of max server memory you will generate internal memory pressure that will cause SQL to release memory back to the OS.    Memory pressure handling has not changed much since SQL 2005 and it was described in detail in a blog post by Slava Oks.   Note that SQL Server Express is an exception to the above behavior. Unlike other editions it does not assume it is the most important process running on the system but tries to be more “desktop” friendly. It will empty its working set after a period of inactivity.   How does SQL Server respond to changing OS memory?    In SQL Server 2005 support for Hot-Add memory was introduced. This feature, available in Enterprise and above editions, allows the server to make use of any extra physical memory that was added after SQL Server started. Being able to add physical memory when the system is running is limited to specialized hardware, but with the Hyper-V Dynamic Memory feature, when new memory is allocated to a guest virtual machine, it looks like hot-add physical memory to the guest. What this means is that thanks to the hot-add memory feature, SQL Server 2005 and higher can dynamically grow if more “physical” memory is granted to a guest VM by Hyper-V dynamic memory.   SQL Server checks OS memory every second and dynamically adjusts its “target” (based on available OS memory and max server memory) accordingly.   In “Denali” Standard Edition will also have sqlserver.exe support for hot-add memory when running virtualized (i.e. detecting and acting on Hyper-V Dynamic Memory allocations).   How does a SQL Server workload in a guest VM impact Hyper-V dynamic memory scheduling?   When a SQL workload causes the sqlserver.exe process to grow its working set, the Hyper-V memory scheduler will detect memory pressure in the guest VM and add memory to it. SQL Server will then detect the extra memory and grow according to workload demand. In our tests we have seen this feedback process cause a guest VM to grow quickly in response to SQL workload - we are still working on characterizing this ramp-up.    How does SQL Server respond when Hyper-V removes memory from a guest VM through ballooning?   If pressure from other VM's cause Hyper-V Dynamic Memory to take memory away from a VM through ballooning (allocating memory with a virtual device driver and returning it to the host OS), Windows Memory Manager will page out unlocked portions of memory and signal low resource notification events. When SQL Server detects these events it will shrink memory until the low memory notifications stop (see cache shrinking description above).    This raises another question. Can we make SQL Server release memory more readily and hence behave more "dynamically" without compromising performance? In certain circumstances where the application workload is predictable it may be possible to have a job which varies "max server memory" according to need, lowering it when the engine is inactive and raising it before a period of activity. This would have limited applicaability but it is something we're looking into.   What Memory Management changes are there in SQL Server “Denali”?   In SQL Server “Denali” (aka SQL11) the Memory Manager has been re-written to be more efficient. The main changes are summarized in this post. An important change with respect to Hyper-V Dynamic Memory support is that now the max server memory setting includes any size page allocations and managed CLR procedure allocations it now represents a closer approximation to total sqlserver.exe memory usage. This makes it easier to calculate a value for max server memory, which becomes important when configuring virtual machines to work well with Hyper-V Dynamic Memory Startup and Maximum RAM settings.   Another important change is no more AWE or hot-add support for 32-bit edition. This means if you're running a 32-bit edition of Denali you're limited to a 4GB address space and will not be able to take advantage of dynamically added OS memory that wasn't present when SQL Server started (though Hyper-V Dynamic Memory is still a supported configuration).   In part 3 we’ll develop some best practices for configuring and using SQL Server with Dynamic Memory. Originally posted at http://blogs.msdn.com/b/sqlosteam/

  Read the article

• Can T-SQL function return user-defined table type?

  - by abatishchev

  I have my own type: CREATE TYPE MyType AS TABLE ( foo INT ) and a function receiving it as a parameter: CREATE FUNCTION Test ( @in MyType READONLY ) RETURNS @return MyType AS ... can it return MyType or only TABLE repeating MyType's structure: CREATE FUNCTION Test ( @in MyType READONLY ) RETURNS @return TABLE (foo INT) AS ... ?

  Read the article

• Cascading Deletes in SQL Sever 2008 not working.

  - by Vaccano

  I have the following table setup. Bag | +-> BagID (Guid) +-> BagNumber (Int) BagCommentRelation | +-> BagID (Int) +-> CommentID (Guid) BagComment | +-> CommentID (Guid) +-> Text (varchar(200)) BagCommentRelation has Foreign Keys to Bag and BagComment. So, I turned on cascading deletes for both those Foreign Keys, but when I delete a bag, it does not delete the Comment row. Do need to break out a trigger for this? Or am I missing something? (I am using SQL Server 2008) Note: Posting requested SQL. This is the defintion of the BagCommentRelation table. (I had the type of the bagID wrong (I thought it was a guid but it is an int).) CREATE TABLE [dbo].[Bag_CommentRelation]( [Id] [int] IDENTITY(1,1) NOT NULL, [BagId] [int] NOT NULL, [Sequence] [int] NOT NULL, [CommentId] [int] NOT NULL, CONSTRAINT [PK_Bag_CommentRelation] PRIMARY KEY CLUSTERED ( [BagId] ASC, [Sequence] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Bag_CommentRelation] WITH CHECK ADD CONSTRAINT [FK_Bag_CommentRelation_Bag] FOREIGN KEY([BagId]) REFERENCES [dbo].[Bag] ([Id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Bag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Bag] GO ALTER TABLE [dbo].[Bag_CommentRelation] WITH CHECK ADD CONSTRAINT [FK_Bag_CommentRelation_Comment] FOREIGN KEY([CommentId]) REFERENCES [dbo].[Comment] ([CommentId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Bag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Comment] GO The row in this table deletes but the row in the comment table does not.

  Read the article

• Is this overkill? Using MDX queries and cubes instead of SQL stored procedures

  - by Jason Holland

  I am new to Microsoft's SQL Server Analysis Services Cubes and MDX queries. Where I work we have a daily sales table in SQL Server 2005 that already contains an aggregate of sale information per store per day. At this time it contains only 164,000+ rows. We have a sales cube dedicated to this table that about 15 reports are based off of. Now, I should also note that we generate reports based on our own fiscal year criteria: a 13 period year (1 month equals 28 days etc.). Is this overkill? At what point is it justified to begin using SSAS Cubes/MDX over plain old SQL Server stored procedures? Since I have always been just using plain old SQL am I tragically late to the MDX party?

  Read the article

• Manage SQL Server Connectivity through Windows Azure Virtual Machines Remote PowerShell

  - by SQLOS Team

  Manage SQL Server Connectivity through Windows Azure Virtual Machines Remote PowerShell Blog This blog post comes from Khalid Mouss, Senior Program Manager in Microsoft SQL Server. Overview The goal of this blog is to demonstrate how we can automate through PowerShell connecting multiple SQL Server deployments in Windows Azure Virtual Machines. We would configure TCP port that we would open (and close) though Windows firewall from a remote PowerShell session to the Virtual Machine (VM). This will demonstrate how to take the advantage of the remote PowerShell support in Windows Azure Virtual Machines to automate the steps required to connect SQL Server in the same cloud service and in different cloud services.  Scenario 1: VMs connected through the same Cloud Service 2 Virtual machines configured in the same cloud service. Both VMs running different SQL Server instances on them. Both VMs configured with remote PowerShell turned on to be able to run PS and other commands directly into them remotely in order to re-configure them to allow incoming SQL connections from a remote VM or on premise machine(s). Note: RDP (Remote Desktop Protocol) is kept configured in both VMs by default to be able to remote connect to them and check the connections to SQL instances for demo purposes only; but not actually required. Step 1 – Provision VMs and Configure Ports   Provision VM1; named DemoVM1 as follows (see examples screenshots below if using the portal):   Provision VM2 (DemoVM2) with PowerShell Remoting enabled and connected to DemoVM1 above (see examples screenshots below if using the portal): After provisioning of the 2 VMs above, here is the default port configurations for example: Step2 – Verify / Confirm the TCP port used by the database Engine By the default, the port will be configured to be 1433 – this can be changed to a different port number if desired.   1. RDP to each of the VMs created below – this will also ensure the VMs complete SysPrep(ing) and complete configuration 2. Go to SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for <SQL instance> -> TCP/IP - > IP Addresses   3. Confirm the port number used by SQL Server Engine; in this case 1433 4. Update from Windows Authentication to Mixed mode   5.       Restart SQL Server service for the change to take effect 6.       Repeat steps 3., 4., and 5. For the second VM: DemoVM2 Step 3 – Remote Powershell to DemoVM1 Enter-PSSession -ComputerName condemo.cloudapp.net -Port 61503 -Credential <username> -UseSSL -SessionOption (New-PSSessionOption -SkipCACheck -SkipCNCheck) Your will then be prompted to enter the password. Step 4 – Open 1433 port in the Windows firewall netsh advfirewall firewall add rule name="DemoVM1Port" dir=in localport=1433 protocol=TCP action=allow Output: netsh advfirewall firewall show rule name=DemoVM1Port Rule Name:                            DemoVM1Port ---------------------------------------------------------------------- Enabled:                              Yes Direction:                            In Profiles:                             Domain,Private,Public Grouping:                             LocalIP:                              Any RemoteIP:                             Any Protocol:                             TCP LocalPort:                            1433 RemotePort:                           Any Edge traversal:                       No Action:                               Allow Ok. Step 5 – Now connect from DemoVM2 to DB instance in DemoVM1 Step 6 – Close port 1433 in the Windows firewall netsh advfirewall firewall delete rule name=DemoVM1Port Output: Deleted 1 rule(s). Ok. netsh advfirewall firewall show  rule name=DemoVM1Port No rules match the specified criteria.   Step 7 – Try to connect from DemoVM2 to DB Instance in DemoVM1  Because port 1433 has been closed (in step 6) in the Windows Firewall in VM1 machine, we can longer connect from VM3 remotely to VM1. Scenario 2: VMs provisioned in different Cloud Services 2 Virtual machines configured in different cloud services. Both VMs running different SQL Server instances on them. Both VMs configured with remote PowerShell turned on to be able to run PS and other commands directly into them remotely in order to re-configure them to allow incoming SQL connections from a remote VM or on on-premise machine(s). Note: RDP (Remote Desktop Protocol) is kept configured in both VMs by default to be able to remote connect to them and check the connections to SQL instances for demo purposes only; but not actually needed. Step 1 – Provision new VM3 Provision VM3; named DemoVM3 as follows (see examples screenshots below if using the portal): After provisioning is complete, here is the default port configurations: Step 2 – Add public port to VM1 connect to from VM3’s DB instance Since VM3 and VM1 are not connected in the same cloud service, we will need to specify the full DNS address while connecting between the machines which includes the public port. We shall add a public port 57000 in this case that is linked to private port 1433 which will be used later to connect to the DB instance. Step 3 – Remote Powershell to DemoVM1 Enter-PSSession -ComputerName condemo.cloudapp.net -Port 61503 -Credential <UserName> -UseSSL -SessionOption (New-PSSessionOption -SkipCACheck -SkipCNCheck) You will then be prompted to enter the password.   Step 4 – Open 1433 port in the Windows firewall netsh advfirewall firewall add rule name="DemoVM1Port" dir=in localport=1433 protocol=TCP action=allow Output: Ok. netsh advfirewall firewall show rule name=DemoVM1Port Rule Name:                            DemoVM1Port ---------------------------------------------------------------------- Enabled:                              Yes Direction:                            In Profiles:                             Domain,Private,Public Grouping:                             LocalIP:                              Any RemoteIP:                             Any Protocol:                             TCP LocalPort:                            1433 RemotePort:                           Any Edge traversal:                       No Action:                               Allow Ok.   Step 5 – Now connect from DemoVM3 to DB instance in DemoVM1 RDP into VM3, launch SSM and Connect to VM1’s DB instance as follows. You must specify the full server name using the DNS address and public port number configured above. Step 6 – Close port 1433 in the Windows firewall netsh advfirewall firewall delete rule name=DemoVM1Port   Output: Deleted 1 rule(s). Ok. netsh advfirewall firewall show  rule name=DemoVM1Port No rules match the specified criteria.  Step 7 – Try to connect from DemoVM2 to DB Instance in DemoVM1  Because port 1433 has been closed (in step 6) in the Windows Firewall in VM1 machine, we can no longer connect from VM3 remotely to VM1. Conclusion Through the new support for remote PowerShell in Windows Azure Virtual Machines, one can script and automate many Virtual Machine and SQL management tasks. In this blog, we have demonstrated, how to start a remote PowerShell session, re-configure Virtual Machine firewall to allow (or disallow) SQL Server connections. References SQL Server in Windows Azure Virtual Machines   Originally posted at http://blogs.msdn.com/b/sqlosteam/

  Read the article

< Previous Page | 45 46 47 48 49 50 51 52 53 54 55 56  | Next Page >