sql server 2008 database option - transact - sql set database option using transact - sql database administration.
Views: 690 R.N.A. Creation
Now, what happens when you have successfully created a reference, and now you try deleting the parent? This is where the ON DELETE clause comes in. This is essentially how we configure how strict a foreign key is. There are 4 options you can give for an ON DELETE: Standard SQL Server NO ACTION Yes (Default) CASCADE Yes SET NULL Yes SET DEFAULT Yes Now the default is NO ACTION. This means that when you try to delete a parent row, SQL Server is going to throw an error and not let you. Cascade means that if you delete the parent row, any rows that reference that parent will also be deleted. This is pretty dangerous and not often recommended. SET NULL will set the foreign key to be NULL. Now obviously, this is going to require that the foreign key is not labelled NOT NULL. Finally, set DEFAULT will change the reference to the child row to some default value. We have not discussed defaults, but a column can have a default value. For example, we could make a deleted user in our Users table, and set the default for the foreign key to be the deleted user, and when any other users are deleted it will default to the deleted user UserId. In addition to the ON DELETE clause, there is the ON UPDATE clause. This one is a little less common because it configures what happens when a parent value changes. Obviously, when you are referencing a primary key, the ON UPDATE clause is nearly useless. That's because the primary key value is never supposed to change. If, on the other hand, we have a foreign key referencing a UNIQUE column that is not a primary key, it may change occasionally. So the only times you have to worry about the ON UPDATE clause is with foreign keys referencing UNIQUE columns that are not a primary key, and when you have natural keys that break the rules and actually do change at some point in time. The ON UPDATE clause has the same options as the ON DELETE clause and they all work the same way. Now, the last thing you need to know about foreign keys is that they must match the data type of the column you are referencing. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 6571 Caleb Curry
How to configure SQL Server 2014/2016 to accept remote connections over TCP/IP using SQL Server Configuration Manager, open the port on firewall for incoming connection. Website: http://foxlearn.com This video shows you how to: Enable Remote Connection to SQL Server, Allow remote connections to SQL Server, Configure SQL Server to Allow Network Connections How to Enable Remote Connection to SQL Server is a Solution for Error: 40 How to configure SQL server 2014 (SQL server 2012) on network How to configuration in SQL Server 2014 (SQL server 2012) How to Configure SQL Server 2014 (SQL Server 2012) to Allow Network Connections Resolving SQL Server Connection Error 26 Configuring SQL Server 2014 (SQL Server 2012) Remote Access Allowing SQL Server 2014 (SQL Server 2012) external connections Allow remote connections to SQL Server 2014 (SQL Server 2012) : How to Video Fix error Cannot Connect to Server (SQL Server) Configuring MSSQL 2014 (MSSQL 2012) Server to Access Database via Network using SQL Server Authentication SQL Server Configuration Manager Options in SQL Server Named Pipes Provider, error: 40 --Could not open a connection to SQL Server Microsoft SQL Server Configure (Server - Client) SQL Server 2014 (SQL Server 2012) offer Network TCP/IP Microsoft SqlServer 2014 (SQL Server 2012) TCP/IP settings
Views: 54843 Fox Learn
create sequence in sql server In this video we will discuss sequence object in SQL Server. Sequence object Introduced in SQL Server 2012 Generates sequence of numeric values in an ascending or descending order Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/sequence-object-in-sql-server-2012.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/sequence-object-in-sql-server-2012_26.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists Creating the Incrementing Sequence : The following code create a Sequence object that starts with 1 and increments by 1 CREATE SEQUENCE [dbo].[SequenceObject] AS INT START WITH 1 INCREMENT BY 1 Generating the Next Sequence Value : Now we have a sequence object created. To generate the sequence value use NEXT VALUE FOR clause SELECT NEXT VALUE FOR [dbo].[SequenceObject] Output : 1 Every time you execute the above query the sequence value will be incremented by 1. I executed the above query 5 times, so the current sequence value is 5. Retrieving the current sequence value : If you want to see what the current Sequence value before generating the next, use sys.sequences SELECT * FROM sys.sequences WHERE name = 'SequenceObject' Alter the Sequence object to reset the sequence value : ALTER SEQUENCE [SequenceObject] RESTART WITH 1 Select the next sequence value to make sure the value starts from 1 SELECT NEXT VALUE FOR [dbo].[SequenceObject] Using sequence value in an INSERT query : CREATE TABLE Employees ( Id INT PRIMARY KEY, Name NVARCHAR(50), Gender NVARCHAR(10) ) -- Generate and insert Sequence values INSERT INTO Employees VALUES (NEXT VALUE for [dbo].[SequenceObject], 'Ben', 'Male') INSERT INTO Employees VALUES (NEXT VALUE for [dbo].[SequenceObject], 'Sara', 'Female') -- Select the data from the table SELECT * FROM Employees Creating the decrementing Sequence : The following code create a Sequence object that starts with 100 and decrements by 1 CREATE SEQUENCE [dbo].[SequenceObject] AS INT START WITH 100 INCREMENT BY -1 Specifying MIN and MAX values for the sequence : Use the MINVALUE and MAXVALUE arguments to specify the MIN and MAX values respectively. Step 1 : Create the Sequence object CREATE SEQUENCE [dbo].[SequenceObject] START WITH 100 INCREMENT BY 10 MINVALUE 100 MAXVALUE 150 Step 2 : Retrieve the next sequence value. The sequence value starts at 100. Every time we call NEXT VALUE, the value will be incremented by 10. SELECT NEXT VALUE FOR [dbo].[SequenceObject] If you call NEXT VALUE, when the value reaches 150 (MAXVALUE), you will get the following error The sequence object 'SequenceObject' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. Recycling Sequence values : When the sequence object has reached it's maximum value, and if you want to restart from the minimum value, set CYCLE option ALTER SEQUENCE [dbo].[SequenceObject] INCREMENT BY 10 MINVALUE 100 MAXVALUE 150 CYCLE At this point, whe the sequence object has reached it's maximum value, and if you ask for the NEXT VALUE, sequence object starts from the minimum value again which in this case is 100. To improve performance, the Sequence object values can be cached using the CACHE option. When the values are cached they are read from the memory instead of from the disk, which improves the performance. When the cache option is specified you can also specify the size of th cache , that is the number of values to cache. The following example, creates the sequence object with 10 values cached. When the 11th value is requested, the next 10 values will be cached again. CREATE SEQUENCE [dbo].[SequenceObject] START WITH 1 INCREMENT BY 1 CACHE 10 Using SQL Server Graphical User Interface (GUI) to create the sequence object : 1. Expand the database folder 2. Expand Programmability folder 3. Right click on Sequences folder 4. Select New Sequence Next video : Difference between SEQUENCE and IDENTITY in SQL Server
Views: 37891 kudvenkat
In this video you will learn following: 1- How to plan backups with AlwaysOn Availability Group in SQL Server? 2- What are the backup options in AlwaysOn Availability Group in SQL Server? 3- What is Preferred Secondary backup option in SQL Server? 4- What is Secondary Only backup Option in SQL Server? 5- What is Primary Only backup Option in SQL Server? 6- What is Any Replica backup Option in SQL Server? 7- What is priority backup Option in SQL Server? 8- How to script backup Jobs for Availability Group in SQL Server? 9- How to create and schedule AG Group database backup jobs on all secondary replica 10- Understanding Auto backup preferences in AG database backups Check out our Website Full DBA Free Tutorial step by Step and other IT tutorials http://www.techbrothersit.com/
Views: 13696 TechBrothersIT
I walk through using a DirectQuery connection with SQL Server and the Power BI service. I start with Power BI Desktop, show how to configure the enterprise gateway, and then show how it looks in the Power BI service. SUBSCRIBE! https://www.youtube.com/channel/UCFp1vaKzpfvoGai0vE5VJ0w?sub_confirmation=1 LET'S CONNECT! Guy in a Cube -- http://twitter.com/guyinacube -- http://www.facebook.com/guyinacube -- http://aka.ms/guyinacube (YouTube) -- https://beme.com/guyinacube -- Snapchat - guyinacube -- https://www.instagram.com/guyinacube/ Adam Saxton (Microsoft Employee) -- http://twitter.com/awsaxton -- https://www.facebook.com/asaxton
Views: 55469 Guy in a Cube
How to Change Color and Fonts in SSMS? Scenario: I am SQL Server developer and I don't like the default fonts used in SQL Server Management Studio, they are too small and I don't like the style. Also I would like to change the color of text. How Can I change text properties as per my choice in SSMS? Solution: SQL Server Management Studio (SSMS) let you customize text properties as you like. Open SSMS, Go to Tools and then Options. Go to Environment and then click on Fonts and Colors. Under options you can make changes as you like. Check out our website for Different SQL Server, MSBI tutorials and interview questions such as SQL Server Reporting Services(SSRS) Tutorial SQL Server Integration Services(SSIS) Tutorial SQL Server DBA Tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 5574 TechBrothersIT
There are hundreds of properties that describe a database. Most of them go far beyond the fundamentals. However, a few need to be addressed right at the start of creating and controlling databases. This video discusses those properties and how to use the ALTER DATABASE command to manipulate them. ALTER DATABASE: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options #sqlserver #fundamentals #database
Views: 111 Grant Fritchey
How to Create Database in SQL Server By using TSQL or GUI -SQL Server/ T-SQL Tutorial Scenario: You are working as database developer and you have to prepare scripts for Create Database with below requirements so it can be run in QA, UAT and Production SQL Server Instances. Database Name : TechBrothersIT Primary File Name : TechBrothersIT Index File Group : TechBrothersIT_Indexe Log File Name : TechBrothersIT and Primary file should be on C:\PrimaryDBFiles Index File should be on C:\IndexDBFiles and Log file should be on C:\LogDBFiles The Initial Size for each of the file should be 1GB and Max Size set to Unlimited and file growth should be set to 2 GB. Solution: You can create the database by using Graphical User interface in SSMS or you can use TSQL. Let's do this by using GUI first and then we will do the same by using TSQL. Step 1: Right Click on the Database Tab and then hit New Database so open Create database wizard. Now make changes according to our requirement. 1 GB=1024 MB. Also we need to add a new File Group for Index File. Now once you hit Ok, new database will be created. How to Create Database by using TSQL Script: If you don't like to create database by using GUI, you can hit Script Button and then Generate Script in new Windows. TSQL script will be generated in new window and you can use this to create database. Also you can make changes as per your environment if paths are different. Link for post for scripts http://www.techbrothersit.com/2016/02/how-to-create-database-in-sql-server-by.html Check out our website for Different SQL Server, MSBI tutorials and interview questions such as SQL Server Reporting Services(SSRS) Tutorial SQL Server Integration Services(SSIS) Tutorial SQL Server DBA Tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 5925 TechBrothersIT
sql server (starting with 2008) no azure database data warehouse parallel. By default the seed and increment are both 1 12 may 2008 an identity column has a name, initial step. How do i add an identity column to table in sql server? Sometime the questions can't find exact syntax for adding property existing. Sql server 2012 auto identity column value jump issue insert into sql table with. Resetting sql server identity columns blackwasp. Managing identity columns with replication in sql server. In these scenarios, see how to replicate tables with setting the identity property on a table when it is created simple task. Identity column in sql server stack overflowidentity wikipediawhy can a table not have more than one identity value falling behind randomly reset how to geek. And when you load it, an error is raised add a row into sql server table that has identity column, the value assigned to column in datatable replaced by generated many databases have ability create automatically incremented numbers. The below snippet will create table called sampletable with id column which has an 3 jun 2015 this is a small example about how to insert rows into setting value for identity on sql server. Auto generated identity column in ms sql server table confirm create with sequence indentity how to an add adding the property existing pro. Sql server performance how to remove the identity column in sql insert records with value ninja code. The metadata is stored at column level not table. My current assignment that i am i've found mapinfo data inserted into sql server spatial is flagged as invalid when try to load it in qgis. Basically the script will do 29 feb 2012 when you import a table with an identity column it is treated like any other regular int datatype. Identity (property) (transact sql) msdn microsoft. Getting an identity column value from sql server ado columns in oracle brent ozar unlimited. An identity column is a in database table that made up of values generated by the microsoft sql server you have options for both seed (starting value) and increment. Seeding and reseeding an identity column is easy relatively safe, if you do it correctly. Sql server identity vs sequence how to define an auto increment primary key in sql table that has reseeding the column eimagine. In sql server, we can use an identity property on a column. This means that as each row is inserted into the table, sql server will automatically increment this microsoft identity columns provide a useful way to generate consecutive numeric values for identifying rows. An example is if 15 jul 2008 microsoft sql server's identity column generates sequential values for new records using a seed value. It would need a rethink though of scalar we are having strange intermittent issue where the identity column randomly, one these tables' values will fall behind, stopping if you using an on your sql server tables, can set next insert value to whatever want. Is there someway to automatically add an identity column when sql server values check thomas larock. when instance is restarted then its auto identity column value jumped based on for customerid column, 'identity(1,1)' specified. Create table with identity column sequence indentity sql server t tutorial 27 jul 2013 here is the question i received on sqlauthority fan page. Sql server identity column? Sql auto increment a field w3schools. Using sql server management studio, i scripted a create to of the existing table and got this [dbo] why does doesn't allow more than one identity column an is ( also known as field ) in database writes 'how can reset not start where it left? ' i've been (this article has updated through 2005. The solution seems to be add an integer 'id' column, identity columns are defined on a table so that the database engine will for many oltp systems in sql server this is often ideal and what i managing with replicated tables your 2005 requires some tlc. Sql how to create table with identity column stack overflow. During 1 aug 2015 difference between sequence and identity in sql server object is introduced 2012, column property the second piece of puzzle constraint, which informs to auto increment numeric value within specified anytime a 21 jul 2016 trying use instead management studio, i have come it says that cannot insert explicit values for 4 sep 2014 when using as your database, you usually an primary key table. Creates an identity column in a table the ms sql server uses keyword to perform auto increment tip specify that 'id' should start at value 10 and by 5, create [dbo].
Views: 12110 Mahasen Powell
In this video you will learn how to setup Database mirroring step by step process. Process explains how to prepare database for Mirroring, how to configure Principle server, what is principle and Mirror server in SQL Server Mirroring, how to configure Mirror server, how to configure witness server, how to failover and how to get required permission for Mirroring configuration in SQL Server. Blog post link for the video: http://sqlage.blogspot.com/2015/03/how-to-setup-database-mirroring-in-sql.html Visit our website to check out SQL Server DBA Tutorial Step by Step http://www.techbrothersit.com/2014/12/sql-server-dba-tutorial.html
Views: 74217 TechBrothersIT
Welcome everyone! Something you need to consider when you are creating foreign keys is what happens if you delete the parent? As a reminder, the parent is the row that has the value you are referencing in the row that has a foreign key. Why is this something important to consider? It's important because foreign keys need to protect us from two primary things, unacceptable INSERT statements, and unacceptable DELETE statements. Let's see what happens when we try to insert incorrect data into the table with the foreign key: INSERT INTO projects VALUES (1, 'Update website homepage', 'CalebCurry') The response tells us plainly that there is no such user in the users table. So this works correctly. Deleting data on the other hand works a bit differently because the database does not know what you want to do with the child row when you delete the parent from the parent table. By default, we will get an error message that prevents the parent from being deleted, but there are some other options. How do we configure this? This is where the ON DELETE statement comes in. We add the keywords ON DELETE right after the foreign key and then we can give it the option of CASCADE or SET NULL. CASCADE means that if we delete the parent, we are also going to delete the child. In our situation what that means is that if somebody creates a project in our project table and then that persons account gets deleted, all of the projects he owns will also be deleted. CASCADE: CREATE TABLE projects( project_id NUMBER, Project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL, CONSTRAINT projects_pk PRIMARY KEY (project_id), CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username) ON DELETE CASCADE ) SET NULL will take the value in the child table and get rid of it. What you are left with is NULL. This means that we have an orphaned child. The first thought you might have is that it is a bad thing to have an orphaned child, but in databases that is not always so. In our application if we had it set to SET NULL, when a user account gets deleted the projects would remain in existence they would just lack a creator. This might be a good thing if you are concerned about the long term survival of a project, this might be the route you want to go. It ultimately depends on the application purpose. If you don't like CASCADE or SET NULL, you can leave the entire ON DELETE statement and just have Oracle throw an error when a parent is deleted. As for us, we are going to use ON DELETE CASCADE. We need to use this with extreme caution. If you are not careful, someday you will run a delete a row and that will cascade through you database deleting a bunch of stuff you didn't want to delete. Stuff happens, so make sure you back up your database every once in eternity. Now, in the last video we started with a database design that had three tables. We've only created two in this video. In the next video we are going to create the next one, which is a little special. Then we'll finish things up by adding some indexes. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me on Patreon! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 19041 Caleb Curry
Hello Friends...!!! Welcome to TechtabInfo my name is Madhu this youtube channel for Technical and it security Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server. The default setting for min server memory is 0, and the default setting for max server memory is 2,147,483,647 megabytes. By default, SQL Server can change its memory requirements dynamically based on available system resources. For more information, see dynamic memory management. The minimum memory amount allowable for max server memory is 128 MB. Important Setting max server memory value too high can cause a single instance of SQL Server might have to compete for memory with other SQL Server instances hosted on the same host. However, setting this value too low could cause significant memory pressure and performance problems. Setting max server memory to the minimum value can even prevent SQL Server from starting. If you cannot start SQL Server after changing this option, start it using the –f startup option and reset max server memory to its previous value. For more information, see Website: https://techtabinfo.com blogs: https://techtabinfo.blogspot.com Facebook: https://www.facebook.com/Techtabinfo-495437450937576 Instagram: https://www.instagram.com/techtabinfo Twitter: https://twitter.com/techtabinfo
Views: 130 Techtab info
In this video you will learn following: 1- What is preferred node order in SQL Server cluster? 2- How to configure preferred node setting in SQL Server cluster? 3- How to change Failover settings (manual or Automatic)? 4- How does preferred node settings work in SQL Server cluster? Blog post link for the video with script if used http://sqlage.blogspot.com/2015/05/how-to-set-preferred-node-order-for-sql.html Complete Step by Step List for DBA Tutorial Videos: http://sqlage.blogspot.com/search/label/SQL%20Sever%202014%20DBA%20Video%20Tutorial
Views: 5625 TechBrothersIT
How to Configure StartUp Environment in SSMS - SQL Server / T-SQL Tutorial Scenario: If you open SQL Server Management Studio, it will ask you provide the SQL Server Instance Name. Once you do that, It opens Object Explorer. You would like to open Object Explorer and New Query window on startup. Solution: SQL Server Management Studio(SSMS) provide us different options for startup. Let's make some changes for Startup options. Step 1: Open SQL Server Management Studio and go to Tools and then Options Step 2: Under the Keyboard, go to Startup and then make changes as shown below. You have following options Open Object Explorer Open New Query Window Open Object Explorer and Query Window Open Object Explorer and Activity Monitor Open Empty Environment You can choose according to what works best for you. You have to close the SSMS and reopen to implement changes you just made. Next time when you will open your SSMS, it will open Object Explorer and Query window for you on startup. Check out our website for Different SQL Server, MSBI tutorials and interview questions such as SQL Server Reporting Services(SSRS) Tutorial SQL Server Integration Services(SSIS) Tutorial SQL Server DBA Tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 4626 TechBrothersIT
Get a free trial of our On-Demand Training Platform: http://pragmaticworks.com/Training/On-Demand-Training/On-Demand-Trial With the latest Azure SQL Database version (v12), Microsoft has continued to improve their SQL cloud platform capabilities. If you aren't looking to leverage Microsoft's Azure platform in your SQL data strategy, you could be missing out on key gains. In this session, we will provide an introduction to Azure SQL Database, including how to get started in the Azure portal, provide some comparisons with Azure SQL Virtual Machines and discuss database migration options.
Views: 20020 Pragmatic Works
This video tutorial covers how to configure ODBC to access a Microsoft SQL Server database. More guides and tutorials: http://www.itgeared.com/.
Views: 304939 itgeared
Set up a new connection to a data destination (SQL Server) with Tanium Connect Tanium Platform Version 7.3 This video shows how a user would create a new connection to a data destination (in this case, SQL Server) using Tanium Connect, and then configure sending options.
Views: 75 Tanium
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching! By Andrew Gould https://www.wiseowl.co.uk - Most of the operations you perform in SQL work against an entire set of records in one go. Cursors, on the other hand, allow you to process a set of records one row at a time. In this video you'll learn how to declare a cursor and how to make it step through a set of records. Along the way you'll learn about the various FETCH statements and additional cursor options. From a practical point of view you'll see how to execute a stored procedure against each record in a table and how to use a cursor to update data in a table by creating a running total column. You can download the script to create the Movies database used in this video at the following link: https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip For more technical details on the various cursor options see: https://msdn.microsoft.com/en-gb/library/ms180169.aspx You can see the range of resources and courses we offer in SQL Server here: https://www.wiseowl.co.uk/sql/ Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
Views: 73821 WiseOwlTutorials
Understanding Replication in SQL Server 2016 1. What are replication components in SQL Server? 2. What is Publisher? 3. What is Distributor? 4. What is Subscriber? 5. Overview of Replication Agents • Snapshot Agent • Distribution Agent • Merge Agent • Log Reader Agent • Queue Reader Agent 6. Replication Jobs on SQL Server 2016 Agent 7. How to troubleshoot Replication using Replication Agents Jobs? Check out our website for Different SQL Server, MSBI tutorials and interview questions such as SQL Server Reporting Services(SSRS) Tutorial SQL Server Integration Services(SSIS) Tutorial SQL Server DBA Tutorial SQL Server 2016 DBA tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 8801 TechBrothersIT
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/exec-vs-spexecutesql-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/exec-vs-spexecutesql-in-sql-server_18.html Dot Net & SQL Server Tutorials https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd In this video we will discuss the difference between exec and sp_executesql. In SQL Servere we have 2 options to execute dynamic sql 1. Exec/Execute 2. sp_executesql We discussed sp_executesql in detail in Part 138 of SQL Server tutorial. Please check out that video if you are new to sp_executesql. If you do a quick search on the internet for the difference between exec and sp_executesql, you will see that many articles on the web states using exec over sp_executesql will have the following 2 problems 1. It open doors for sql injection attacks 2. Cached query plans may not be reused and leads to poor performance This is generally true, but if you use QUOTENAME() function you can avoid sql injection attacks and with sql server auto-parameterisation capability the cached query plans can be reused so performance is also not an issue. Let's understand these with examples. What is exec() in SQL Server Exec() or Execute() function is used to execute dynamic sql and has only one parameter i.e the dynamic sql statement you want to execute. As you can see in the example below, we are concatenating strings to build dynamic sql statements which open doors for sql injection. Declare @FN nvarchar(50) Set @FN = 'John' Declare @sql nvarchar(max) Set @sql = 'Select * from Employees where FirstName = ''' + @FN + '''' Exec(@sql) If we set @FN parameter to something like below, it drops SalesDB database Declare @FN nvarchar(50) Set @FN = ''' Drop Database SalesDB --''' Declare @sql nvarchar(max) Set @sql = 'Select * from Employees where FirstName = ''' + @FN + '''' Exec(@sql) However, we can prevent SQL injection using the QUOTENAME() function as shown below. Declare @FN nvarchar(50) Set @FN = ''' Drop Database SalesDB --''' Declare @sql nvarchar(max) Set @sql = 'Select * from Employees where FirstName = ' + QUOTENAME(@FN,'''') --Print @sql Exec(@sql) Notice with the quotename function we are using a single quote as a delimiter. With the use of this function if there is a single quote in the user input it is doubled. For example, if we set @FN='John', notice the string 'John' is wrapped in single quotes Declare @FN nvarchar(50) Set @FN = 'John' Declare @sql nvarchar(max) Set @sql = 'Select * from Employees where FirstName = ' + QUOTENAME(@FN,'''') Print @sql When the above query is executed the following is the query printed Select * from Employees where FirstName = 'John' Along the same lines, if we try to inject sql, QUOTENAME() function wraps all that input in another pair of single quotes treating it as a value for the FirstName column and prevents SQL injection. With sql server auto-parameterisation capability the cached query plans can be reused. SQL Server can detect parameter values and create parameterised queries on its own, even if you don't explicitly declare them. However, there are exceptions to this. Auto-parameterisation comes in 2 flavours - Simple and Forced. We will discuss auto-parameterisation in detail in a later video. Execute the following DBCC command to remove all entries from the plan cache DBCC FREEPROCCACHE Execute the following query. Notice we have set @FN='Mary' Declare @FN nvarchar(50) Set @FN = 'Mary' Declare @sql nvarchar(max) Set @sql = 'Select * from Employees where FirstName = ' + QUOTENAME(@FN,'''') Exec(@sql) Execute the following query to retrieve what we have in the query plan cache SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY cp.usecounts DESC Notice we have an auto-parameterised query and at the moment usecounts is 1. Now change @FN='Mark' and execute the same query. After the query is completed, retrieve the entries from the plan cache. Notice the usecounts for the auto-parameterised query is 2, suggesting that the same query plan is reused. Along the same lines, if you change @FN='John' and execute the query, you will see that the usecounts is now 3 for the auto-parameterised query. Summary 1. If you use QUOTENAME() function, you can prevent sql injection while using Exec() 2. Cached query plan reusability is also not an issue while using Exec(), as SQL server automatically parameterize queries. 3. I personally prefer using sp_executesql over exec() as we can explicitly parameterise queries instead of relying on sql server auto-parameterisation feature or QUOTENAME() function. I use Exec() only in throw away scripts rather than in production code.
Views: 19412 kudvenkat
In this Super SQL Tip Software Engineer Andrei Rusitoru explains the fantastic new formatting options available in SQL Prompt 8 from Redgate. SQL Prompt is a code completion, code formatting, and refactoring tool for SQL Server. It’ll plug into SQL Server Management Studio or Visual Studio. You’ll find you can work more quickly and accurately with SQL queries, as well as standardize across your team by sharing formatting styles and commonly used SQL snippets. Try it free for 28 days at www.red-gate.com/sqlprompt For more tips for writing SQL effortlessly check out the #SuperSQLTips playlist: https://www.youtube.com/playlist?list=PLhFdCK734P8A_q6XHDXi4CQzys3R0Jw3X Transcript: Hello, I’m Andrei Rusitoru and I work for Redgate software. Today I’m going to show you how to create your own style using the brand new formatting options in SQL Prompt 8. In SQL Prompt 8 we’ve added our completely re-designed formatting engine, giving you the best possible control over your formatting styles. Today we are going to go through the new SQL Prompt formatting options in SQL Server Management Studio, but exactly the same functionality is also available in Visual Studio. In SSMS I’m going to open the SQL Prompt menu at the top here and choose Edit Formatting Styles from the drop-down. This opens the Formatting styles window where you can see we have already included a couple of example styles to get you started. Keep an eye out for more of these in upcoming releases. Under “Your Styles” I’m going to click on Create a style to get started. It’s much easier to build your style on an existing one rather than starting completely from scratch. If you have created styles in previous versions of SQL Prompt then you will find them added here, and you can continue to work on them. I’m going to use Redgate’s Default Style as a base to build my new style on. So here are all the options. As you can see we’ve added a lot of functionality to allow you to tweak your style settings. Each of the main areas of SQL code has a section down the left hand side here, with sub-sections to help you easily find and manage these settings. To start with, let’s take a look at the GLOBAL settings and set the Whitespace options. For my tab behaviour, I want to use only tabs and I want 2 spaces in a tab. As you can see, there is some sample code at the bottom here that updates live as you change your settings so you can see exactly what impact your changes are having. If you want, you can change this to your own code if you have a query open. I don’t want to wrap my lines so I’m going to uncheck that. And finally I want to add a space before my commas. Let’s check how it looks… that looks good so I’m going to press Save. My new style has now been added to the menu on the left, and I can see a preview of what it does right here. This is going to be really helpful when I’ve created a few of these. I can also set the new style as my default active formatting by clicking Set As Active. I’m now going to go back in and change some other settings, so I click edit at the bottom here. I like to have my JOIN clauses right aligned to FROM, so I’m going to change that. I also like to have an extra line between them so I can see them easily. And I want my AND/OR operators to be aligned to the first list item, so I am going to go down here to the EXPRESSIONS section and change that under Operators. As you can see, there are lots of options to help you get your SQL looking exactly the way you want it and you can keep tweaking it until it looks perfect. You can create as many styles as you want too, so if you want a few different versions you can create them and then easily switch between them by simply right-clicking in your code, we are going to cover this in another video soon. So there you have it, it really is that easy to create your own style using the new formatting options in SQL Prompt 8.
Views: 1138 Redgate Videos
See more at: http://www.goengineer.com/products/solidworks/ Learn the entire Microsoft SQL 2014 installation process paying special attention to settings that are recommended for SOLIDWORKS Enterprise PDM. SOLIDWORKS Quick Tip presented by Joseph Cutrona of GoEngineer. About GoEngineer: GoEnginneer delivers software, technology and expertise that enable companies to unlock innovation and deliver better products faster. With more than 30 years experience and thousands of customers in high-tech, medical, machine design, energy and other industries, GoEngineer provides best-in-class design solutions from SOLIDWORKS, Stratasys, CAMWorks and Agile PLM. http://www.goengineer.com http://www.facebook.com/goengineer http://www.twitter.com/goengineer http://www.linkedin.com/goengineer https://plus.google.com/101084524922750716243
Views: 15179 GoEngineer
How to add Line Numbers in SQL Server Management Studio SSMS Scenario: As sql server developer we write a lot of scripts and every time error occur, it say error happened on line number x. if you are using 3 lines code like me, you might be Ok to see which is third line :) but if your have 100,s of lines of code, it is good idea if you can see the line numbers next to code. Question is, How to enable line Numbers and if I will copy this script, will line numbers will be copied as well? Solution: Open SQL Server Management Studio(SSMS). Go to Tools and then Options. Now go to Text Editor, Then Transact-SQL and hit General. On Option windows check the Line Number Box under Display Now you should see the Line Numbers next to your script, and If you will copy the script , it will not copy the line numbers with it. Line Numbers are there only for display. Check out our website for Different SQL Server, MSBI tutorials and interview questions such as SQL Server Reporting Services(SSRS) Tutorial SQL Server Integration Services(SSIS) Tutorial SQL Server DBA Tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 3818 TechBrothersIT
This video is part of the Analyzing and Visualizing Data with Power BI course available on EdX. To sign up for the course, visit: http://aka.ms/pbicourse. To read more: Power BI service https://aka.ms/pbis_gs Power BI Desktop https://aka.ms/pbid_gs Power BI basic concepts tutorial: https://aka.ms/power-bi-tutorial To submit questions and comments about Power BI, please visit community.powerbi.com.
Views: 50604 Microsoft Power BI
This tutorial shows you how to find your server name or instance for connecting to SQL Management Studio (SSMS). Step by step tutorial on how to check if SQL is installed on your machine using the services to check that Microsoft SQL Server has been started. Next how to find your instance name using SQL Server Configuration Manager using SQL 2012 or 2014 folder. I show you how to connect to the instance name in the Connect to Server SQL Sever 2014 box. The Server Name is made up of the following: ServerName/Instance Name 1: To find the Server name type the following into command prompt: SQLCMD -L 2: To find the instance name go to Microsoft configuration manager there you will see your version of sql server in my case sql server express as such I know my instance name is sql server express 3. Put the server name and instance name together as per the video SOPHIA-HP/SQLEXPRESS To request a video: http://itsophy.com/request-video/ More SQL Server videos: http://www.youtube.com/playlist?list=PLrTXCzXE7TKKhIhGtl65lYMAHP6xN5Wa4 My Blog: http://itsophy.com/
Views: 126377 ITSophy
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/03/how-to-check-guid-is-null-or-empty-in.html Slides http://csharp-video-tutorials.blogspot.com/2017/03/how-to-check-guid-is-null-or-empty-in_15.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss how to check if a GUID is null or empty How to check if a GUID is NULL : Checking if a GUID is null is straight forward in SQL Server. Just use IS NULL keywords as shown below. Declare @MyGuid Uniqueidentifier If(@MyGuid IS NULL) Begin Print 'Guid is null' End Else Begin Print 'Guid is not null' End In the above example, since @MyGuid is just declared and not initialised, it prints the message "Guid is null" Now let's say, if a GUID variable is NULL, then we want to initialise that GUID variable with a new GUID value. If it's not NULL, then we want to retain it's value. One way to do this is by using an IF condition as shown below. Declare @MyGuid UniqueIdentifier If(@MyGuid IS NULL) Begin Set @MyGuid = NEWID() End Select @MyGuid We can achieve exactly the same thing by using ISNULL() function. The advantage of using ISNULL() function is that, it reduces the amount of code we have to write. Declare @MyGuid UniqueIdentifier Select ISNULL(@MyGuid, NewID()) How to check if a GUID is EMPTY : Before understanding how to check if a GUID is empty, let's understand what is an empty GUID. An empty GUID is a GUID with all ZEROS as shown below. 00000000-0000-0000-0000-000000000000 How to create this empty GUID. Do we have to type all the ZERO's and Hyphens. The answe is NO. We do not have to type them manually. Instead use one of the following SELECT query's to create an empty GUID. I prefer to use the second SELECT statement as it has only one CAST SELECT CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER) OR SELECT CAST(0x0 AS UNIQUEIDENTIFIER) To check if a GUID is an empty GUID, you have 2 options Option 1: You can compare it to an Empty GUID value as shown below Declare @MyGuid UniqueIdentifier Set @MyGuid = '00000000-0000-0000-0000-000000000000' If(@MyGuid = '00000000-0000-0000-0000-000000000000') Begin Print 'Guid is Empty' End Else Begin Print 'Guid is not Empty' End Option 2: You can also compare it to a return value of the CAST method Declare @MyGuid UniqueIdentifier Set @MyGuid = '00000000-0000-0000-0000-000000000000' If(@MyGuid = Cast(0x0 as Uniqueidentifier)) Begin Print 'Guid is Empty' End Else Begin Print 'Guid is not Empty' End
Views: 15961 kudvenkat
Retrieving the Maximum and Minimum Memory Settings For SQL Server
Views: 314 SQLInSix Minutes
This demo shows how to: Configure a shared data source to store credentials (required for subscriptions) Configure SSRS for SMTP email delivery Create a subscription that delivers reports to a file share Create a subscription that delivers reports via email
Views: 87635 DeveloperVideos
Quick run through the config options of SSIS "Execute SQL Task" with a brief description of what each field is for.
Views: 1244 Steden Videos
In this video you will learn how to create alert in SQL Server using SQL Server management studio as well using T-SQL Script. It walks you through different types of Alerts in SQL Server, how to configure severity settings, how to configure error message search, how to configure Response and configuring different options while creating alert in SQL Server. It also talks about best practices of creating SQL Server Alert using SQL Server Agent. Blog post link for this video: http://sqlage.blogspot.com/2015/03/how-to-create-alert-in-sql-server.html Visit our website to check out SQL Server DBA Tutorial Step by Step http://www.techbrothersit.com/2014/12/sql-server-dba-tutorial.html
Views: 17372 TechBrothersIT
Ensure each SQL Server Port is open and SQL Server 2012 can communicate via Tcp/IP on Windows Server 2008 R2 Firewall after a new SQL Server Install. Ports: TCP 135 - SQL Transact Debugger/RPC TCP 1433 - SQL Server Default Instance TCP 1434 - SQL Server Admin Connection TCP 2382 - SQL Server Browser TCP 2383 - SQL Analysis Services TCP 4022 - SQL Service Broker UDP 1434 - SQL Server Browser Multicast Response Reference: Connecting to SQL Server over the Internet: http://msdn.microsoft.com/en-us/library/ms175483(v=sql.105).aspx
Views: 61506 Jeff Willener
How to change SQL Server Default database location.
Views: 4368 Let's SQL
In this video you will learn: What is AlwaysOn Availability Group in SQL Server 2012/2014? How to enable Availability Group in SQL Server 2014? How to create Availability Groups in SQL Server 2014? How to configure Primary and secondary replica? How to create and configure Availability group listener? Ports requirement of Availability group? How to failover Availability group? Visit our website to check out SQL Server DBA Tutorial Step by Step http://www.techbrothersit.com/2014/12/sql-server-dba-tutorial.html
Views: 88277 TechBrothersIT
Want all of our free Microsoft SQL Server 2012 Exam 70-462 training videos? Visit our Learning Library, which features all of our training courses and tutorials at http://learn.infiniteskills.com?utm_source=youtube&utm_medium=microsoft_sql_70-462&utm_campaign=01839_0319 More details on this Microsoft SQL Server 2012 Exam 70-462 training can be seen at http://www.infiniteskills.com/microsoft/. This clip is one example from the complete course. For more free Microsoft tutorials please visit our main website. YouTube: https://www.youtube.com/user/OreillyMedia Facebook: https://www.facebook.com/OReilly/?fref=ts Twitter: https://twitter.com/OReillyMedia Website: http://www.oreilly.com/
Views: 3974 O'Reilly - Video Training
Configuring minimum memory and maximum memory settings here is the code USE master EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE /*when u run the above statement u are supposed to get a 1 which means success 0 (success) or 1 (failure)*/ USE master EXEC sp_configure 'min server memory (MB)', 2000 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'max server memory (MB)',64000 RECONFIGURE WITH OVERRIDE
Views: 3906 raghu veera
Scenario: You can create shortcut for most commonly used Stored Procedures so you don't have to write every time. You can simple use Shortcut keys to run them. Solution: Open SQL Server Management Studio (SSMS). To to Tools and then go to options. Go ahead and add the Stored Procedures in front of Shortcuts. I have added sp_order user Stored Procedure in front of Ctrl+3. Once you open the New Query and then Press Ctrl+1, it will run sp_who Stored Procedure. You don't have to worry about scope of database as it will run in any database. To use the Shortcut for sp_Order , you need to change the Query scope to Database in which sp_Order exists. If you will run in any other database, it will through an error as Stored Procedure will not be present. Check out our website for Different SQL Server, MSBI tutorials and interview questions such as SQL Server Reporting Services(SSRS) Tutorial SQL Server Integration Services(SSIS) Tutorial SQL Server DBA Tutorial SQL Server / TSQL Tutorial ( Beginner to Advance) http://www.techbrothersit.com/
Views: 4031 TechBrothersIT
SSIS Tutorial Demo Scenario: The demo " How to Load File Name and Record Count after loading each file to SQL Server Table". To tell you more details about scenario, you can think of getting multiple files during the day. You want to create an SSIS Package that loads the file/s and then log file name, record loaded and time to the dbo.FileLoadInformation Table. In this video we will learn how to use Insert Query with Parameters in Execute SQL Task. We will be learning following items How to Create an SSIS Package How to Load Data From Flat File to SQL Server Table How to use For-each Loop Container to Loop through set of Flat Files How to use Row Count Transformation in SSIS Package How to use Execute SQL Task in SSIS Package to insert variable values to a Table Blog post link for the video with scripts http://sqlage.blogspot.com/2015/04/how-to-use-insert-query-with-parameters.html Check out step by Step SQL Server Integration Services(SSIS) Tutorial by TechBrothers http://www.techbrothersit.com/2014/12/ssis-videos.html
Views: 9471 TechBrothersIT
Demonstration of calling "Execute SQL Task" to load some SSIS variables from a one row result set. Includes error handling.
Views: 15108 Steden Videos
debugging in ssms debugging t-sql code t sql debug stored procedure sql server management studio debug stored procedure how to debug t sql debug in sql server management studio In this video we will discuss how to debug stored procedures in SQL Server. Setting up the Debugger in SSMS : If you have connected to SQL Server using (local) or . (period), and when you start the debugger you will get the following error Unable to start T-SQL Debugging. Could not connect to computer. To fix this error, use the computer name to connect to the SQL Server instead of using (local) or . For the examples in this video we will be using the following stored procedure. Create procedure spPrintEvenNumbers @Target int as Begin Declare @StartNumber int Set @StartNumber = 1 while(@StartNumber [ @Target) Begin If(@StartNumber%2 = 0) Begin Print @StartNumber End Set @StartNumber = @StartNumber + 1 End Print 'Finished printing even numbers till ' + RTRIM(@Target) End Connect to SQL Server using your computer name, and then execute the above code to create the stored procedure. At this point, open a New Query window. Copy and paste the following T-SQL code to execute the stored procedure. DECLARE @TargetNumber INT SET @TargetNumber = 10 EXECUTE spPrintEvenNumbers @TargetNumber Print 'Done' Starting the Debugger in SSMS : There are 2 ways to start the debugger 1. In SSMS, click on the Debug Menu and select Start Debugging 2. Use the keyboard shortcut ALT + F5 At this point you should have the debugger running. The line that is about to be executed is marked with an yellow arrow Step Over, Step into and Step Out in SSMS : You can find the keyboard shortcuts in the Debug menu in SSMS. Let us understand what Step Over, Step into and Step Out does when debugging the following piece of code 1. There is no difference when you STEP INTO (F11) or STEP OVER (F10) the code on LINE 2 2. On LINE 3, we are calling a Stored Procedure. On this statement if we press F10 (STEP OVER), it won't give us the opportunity to debug the stored procedure code. To be able to debug the stored procedure code you will have to STEP INTO it by pressing F11. 3. If the debugger is in the stored procedure, and you don't want to debug line by line with in that stored procedure, you can STEP OUT of it by pressing SHIFT + F11. When you do this, the debugger completes the execution of the stored procedure and waits on the next line in the main query, i.e on LINE 4 in this example. To stop debugging : There are 2 ways to stop debugging 1. In SSMS, click on the Debug Menu and select Stop Debugging 2. Use the keyboard shortcut SHIFT + F5 Show Next Statement shows the next statement that the debugger is about to execute. Run to Cursor command executes all the statements in a batch up to the current cursor position Locals Window in SSMS : Displays the current values of variables and parameters If you cannot see the locals window or if you have closed it and if you want to open it, you can do so using the following menu option. Locals window is only available if you are in DEBUG mode. Watch Window in SSMS : Just like Locals window, Watch window is used to watch the values of variables. You can add and remove variables from the watch window. To add a variable to the Watch Window, right click on the variable and select "Add Watch" option from the context menu. Call Stack Window in SSMS : Allows you to navigate up and down the call stack to see what values your application is storing at different levels. It's an invaluable tool for determining why your code is doing what it's doing. Immediate Window in SSMS : Very helpful during debugging to evaluate expressions, and print variable values. To clear immediate window type ]cls and press enter. Breakpoints in SSMS : There are 2 ways to set a breakpoint in SSMS. 1. By clicking on the grey margin on the left hand side in SSMS (to remove click again) 2. By pressing F9 (to remove press F9 again) Enable, Disable or Delete all breakpoints : There are 2 ways to Enable, Disable or Delete all breakpoints 1. From the Debug menu 2. From the Breakpoints window. To view Breakpoints window select Debug =] Windows =] Breakpoints or use the keyboard shortcut ALT + CTRL + B Conditional Breakpoint : Conditional Breakpoints are hit only when the specified condition is met. These are extremely useful when you have some kind of a loop and you want to break, only when the loop variable has a specific value (For example loop varible = 100). How to set a conditional break point in SSMS : 1. Right click on the Breakpoint and select Condition from the context menu 2. In the Breakpoint window specify the condition Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/debugging-sql-server-stored-procedures.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/debugging-sql-server-stored-procedures_28.html
Views: 116712 kudvenkat
The video shows the basics for setting up SSRS security. http://www.ReportingGuru.com Email us at [email protected] if you need reporting help, custom reports, or reporting architecture setup. Our phone number is 1-(800) 921-4759. YouTube Channel for Tips, Overviews, Tutorials: https://www.youtube.com/user/ReportingGurus Reporting Guru is a US based development company with all resources located in the US. We have many senior level developers with decades of development experience. Please let us know if you would like to discuss your requirements or issues free of charge. Our process is taking your requirements and suggesting the best architecture or approach without trying to sell you any specific software. We work as needed and only charge for the hours we work. We do not charge a retainer and there is no minimum charge. We offer the following services: Custom Report Writing | Consulting | Database Development & Integration. Some of our specialties are: -- SQL Server Reporting Services SSRS / SQL Server / SQL Server Integration -- Crystal Reports / Business Objects -- BIRT, Jasper, Pentaho, BIRT iHub -- Dashboards and Cloud Reporting Solutions -- Tableau -- Cognos -- Services SSIS / SQL Server Analysis Services SSAS -- Complex Database Queries -- Oracle, MySQL -- Access, Excel, and Pivot Tables -- Various ERPs such SAGE, Peachtree, Epicor, BOLO, SAP, Maximo, Fishbowl, Infor10, JD Edwards, PeopleSoft, QuickBooks, SalesForce, Timberline, UFIDA, Microsoft Dynamics, Yardi, Concur, PMWeb CUSTOM REPORT WRITING Our experienced data report writers take your report & business requirements to build the custom reports you need. We deliver reports on demand or on a timed schedule. CONSULTING When your data and reporting team needs guidance or whether you need to build new reports, convert reports, enhance existing reports or need advice on finding the right reporting solution for your business ReportingGuru is here to help. DATABASE DEVELOPMENT & INTEGRATION We create and develop the necessary structure to house business data in a clear and easily accessible manner, so you have the tools to pull the reports you need easily.
Views: 19672 Reporting Guru
AlwaysOn Availability Groups (SQL Server) The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations. An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log. In this Topic: • Benefits • Terms and Definitions • Interoperability and Coexistence with Other Database Engine Features • Related Tasks • Related Content Benefits AlwaysOn Availability Groups provides a rich set of options that improve database availability and that enable improved resource use. The key components are as follows: Supports up to five availability replicas. An availability replica is an instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Each availability group supports one primary replica and up to four secondary replicas. For more information, see Overview of AlwaysOn Availability Groups (SQL Server). Important Each availability replica must reside on a different node of a single Windows Server Failover Clustering (WSFC) cluster. For more information about prerequisites, restrictions, and recommendations for availability groups, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server). ===========================================
Views: 17463 Enayat Meer
SQL AlwaysOn High Availability Group video 3: (SQL Server) Database replica Servers configuration with Primary and Secondary Database servers. Replicaton of 3 databases has been shared in this video. ----------------------------Details about AOHA groups---------------------- The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations. An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log. In this Topic: • Benefits • Terms and Definitions • Interoperability and Coexistence with Other Database Engine Features • Related Tasks • Related Content Benefits AlwaysOn Availability Groups provides a rich set of options that improve database availability and that enable improved resource use. The key components are as follows: Supports up to five availability replicas. An availability replica is an instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Each availability group supports one primary replica and up to four secondary replicas. For more information, see Overview of AlwaysOn Availability Groups (SQL Server). Important Each availability replica must reside on a different node of a single Windows Server Failover Clustering (WSFC) cluster. For more information about prerequisites, restrictions, and recommendations for availability groups, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).
Views: 5937 Enayat Meer
This Video is to show How to connect SQL Server through Internet: Steps: Find out IP Address for SQL Server machine Make to Static IP Address Find Port Address for SQL Server For Default it's 1433 For Name Instances it varies so make it Static Port Number Install and Enable SQL Server Browser Open a Firewall Settings for SQL Server and SQL Server Browser Enable the Port Forwarding on you Router with SQL server ports
Views: 229133 Kranthi Kumar
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/03/dynamic-sql-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2017/03/dynamic-sql-in-sql-server_27.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss 1. What is Dynamic SQL 2. Simple example of using Dynamic SQL What is Dynamic SQL Dynamic SQL is a SQL built from strings at runtime. Simple example of using Dynamic SQL Here is the SQL Script to create Employees table and populate it with data Create table Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) Go Insert into Employees values ('Mark', 'Hastings', 'Male', 60000) Insert into Employees values ('Steve', 'Pound', 'Male', 45000) Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Philip', 'Hastings', 'Male', 45000) Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000) Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000) Insert into Employees values ('John', 'Stanmore', 'Male', 80000) Go One way to achieve this is by implementing a stored procedure as shown below that this page would call. Create Procedure spSearchEmployees @FirstName nvarchar(100), @LastName nvarchar(100), @Gender nvarchar(50), @Salary int As Begin Select * from Employees where (FirstName = @FirstName OR @FirstName IS NULL) AND (LastName = @LastName OR @LastName IS NULL) AND (Gender = @Gender OR @Gender IS NULL) AND (Salary = @Salary OR @Salary IS NULL) End Go The stored procedure in this case is not very complicated as we have only 4 search filters. What if there are 20 or more such filters. This stored procedure can get complex. To make things worse what if we want to specify conditions like AND, OR etc between these search filters. The stored procedure can get extremely large, complicated and difficult to maintain. One way to reduce the complexity is by using dynamic SQL as show below. Depending on for which search filters the user has provided the values on the "Search Page", we build the WHERE clause dynamically at runtime, which can reduce complexity. However, you might hear arguments that dynamic sql is bad both in-terms of security and performance. This is true if the dynamic sql is not properly implemented. From a security standpoint, it may open doors for SQL injection attack and from a performance standpoint, the cached query plans may not be reused. If properly implemented, we will not have these problems with dynamic sql. In our upcoming videos, we will discuss good and bad dynamic sql implementations. For now let's implement a simple example that makes use of dynamic sql. In the example below we are assuming the user has supplied values only for FirstName and LastName search fields. To execute the dynamicl sql we are using system stored procedure sp_executesql. sp_executesql takes two pre-defined parameters and any number of user-defined parameters. @statement - The is the first parameter which is mandatory, and contains the SQL statements to execute @params - This is the second parameter and is optional. This is used to declare parameters specified in @statement The rest of the parameters are the parameters that you declared in @params, and you pass them as you pass parameters to a stored procedure Declare @sql nvarchar(1000) Declare @params nvarchar(1000) Set @sql = 'Select * from Employees where [email protected] and [email protected]' Set @params = '@FirstName nvarchar(100), @LastName nvarchar(100)' Execute sp_executesql @sql, @params, @FirstName='Ben',@LastName='Hoskins' This is just the introduction to dynamic SQL. If a few things are unclear at the moment, don't worry. In our upcoming videos we will discuss the following 1. Implementing a real world "Search Web Page" with and without dynamic SQL 2. Performance and Security implications of dynamic sql. Along the way we will also discuss good and bad dynamic sql implementations. 3. Different options available for executing dynamic sql and their implications 4. Using dynamic sql in stored procedures and it's implications Once we discuss all the above, you will understand 1. The flexibility dynamic sql provides 2. Advantages and disadvantages of dynamic sql 3. When and when not to use dynamic sql
Views: 46797 kudvenkat